SQL question challenge (Friends)

Link to Github

DDL Scripts

create table table1(time date,user_id int,app varchar(40),event varchar(40));

 insert into table1 values('2018-10-01',1,'facebook','processed');
 insert into table1 values('2018-10-01',1,'facebook','sent');
 insert into table1 values('2018-10-01',1,'facebook','accepted');
 insert into table1 values('2018-10-01',2,'facebook','sent');
 insert into table1 values('2018-10-02',2,'facebook','accepted');
 insert into table1 values('2018-10-01',3,'facebook','sent');

 select * from table1;
 +------------+---------+----------+-----------+
 | time       | user_id | app      | event     |
 +------------+---------+----------+-----------+
 | 2018-10-01 |       1 | facebook | processed |
 | 2018-10-01 |       1 | facebook | sent      |
 | 2018-10-01 |       1 | facebook | accepted  |
 | 2018-10-01 |       2 | facebook | sent      |
 | 2018-10-02 |       2 | facebook | accepted  |
 | 2018-10-01 |       3 | facebook | sent      |
 +------------+---------+----------+-----------+

SQL question challenge (Continents)

Link to github

Table with the columns: continents: continent, country, population

CREATE TABLE continents (
   continent          varchar(80),
   country            varchar(80),
   population         int
  );

  INSERT INTO continents VALUES ('Asia','China', 100);
  INSERT INTO continents VALUES ('Asia','India', 100);
  INSERT INTO continents VALUES ('Africa','South Africa', 50);
  INSERT INTO continents VALUES ('Africa','Egypt', 20);
  INSERT INTO continents VALUES ('North America','USA', 50);
  INSERT INTO continents VALUES ('North America','Canada', 50);

SQL question challenge (Contents)

Link to github

  Table: content_id | content_type (comment/ post) | target_id
           If it is comment，target_id is the userid who posts it.
           If it is post, then target_id is NULL.

DDL Scripts

CREATE TABLE contents(
  content_id int,
  content_type varchar(80),
  target_id varchar(80));

  INSERT INTO contents VALUES (1,'comment','u1');
  INSERT INTO contents VALUES (2,'post',NULL);
  INSERT INTO contents VALUES (3,'comment','u2');
  INSERT INTO contents VALUES (4,'post',NULL);
  INSERT INTO contents VALUES (5,'comment','u1');
  INSERT INTO contents VALUES (6,'comment','u1');
  INSERT INTO contents VALUES (7,'comment','u3');
  INSERT INTO contents VALUES (8,'post',NULL);
  INSERT INTO contents VALUES (9,'video','u1');
  INSERT INTO contents VALUES (10,'video','u3');
  INSERT INTO contents VALUES (11,'post',NULL);
  INSERT INTO contents VALUES (12,'photo','u1');
  INSERT INTO contents VALUES (13,'photo','u3');
  INSERT INTO contents VALUES (14,'photo','u1');
  INSERT INTO contents VALUES (15,'article','u3');

SQL question challenge (Companies)

Link to github

DDL Scripts

CREATE TABLE companies (
   member_id            int,
   company_name       varchar(80),
   year_start      int
  );

  INSERT INTO companies VALUES (1,'Google', 1990);
  INSERT INTO companies VALUES (1,'Microsoft', 2000);
  INSERT INTO companies VALUES (2,'Microsoft', 2000);
  INSERT INTO companies VALUES (2,'Google', 2001);
  INSERT INTO companies VALUES (3,'Microsoft', 1997);
  INSERT INTO companies VALUES (3,'Google', 1998);
  INSERT INTO companies VALUES (4,'Microsoft', 1997);
  INSERT INTO companies VALUES (4,'LinkedIn', 1998);
  INSERT INTO companies VALUES (4,'Google', 2000);
  +-----------+--------------+------------+
  | member_id | company_name | year_start |
  +-----------+--------------+------------+
  |         1 | Google       |       1990 |
  |         1 | Microsoft    |       2000 |
  |         2 | Microsoft    |       2000 |
  |         2 | Google       |       2001 |
  |         3 | Microsoft    |       1997 |
  |         3 | Google       |       1998 |
  |         4 | Microsoft    |       1997 |
  |         4 | LinkedIn     |       1998 |
  |         4 | Google       |       2000 |
  +-----------+--------------+------------+

SQL question challenge (Comments)

Link to Github

DDL Scripts

CREATE TABLE comments (
  name            varchar(80),
  posts           varchar(80),
  comments         int
 );

 INSERT INTO comments VALUES ('u1', 'page1', '90');
 INSERT INTO comments VALUES ('u1', 'page2', '50');
 INSERT INTO comments VALUES ('u1', 'page3', '40');
 INSERT INTO comments VALUES ('u2', 'page2', '55');
 INSERT INTO comments VALUES ('u2', 'page4', '45');
 INSERT INTO comments VALUES ('u4', 'page4', '30');
 INSERT INTO comments VALUES ('u4', 'page3', '40');
 INSERT INTO comments VALUES ('u3', 'page2', '100');

 Comments I
 name posts comments
 u1 page1 90
 u1 page2 50
 u1 page3 40
 u2 page2 55
 u2 page4 45
 u4 page4 30
 u4 page3 40
 u3 page2 100

SQL question challenge (Article views)

Link to github

DDL Scripts

CREATE TABLE article_views (
   date            timestamp,
   viewer_id       int,
   article_id      int,
   author_id       int
  );
  
  INSERT INTO article_views VALUES ('2017-08-01',123, 456 ,789);
  INSERT INTO article_views VALUES ('2017-08-02',432 ,543, 654);
  INSERT INTO article_views VALUES ('2017-08-01',789, 456 ,789);
  INSERT INTO article_views VALUES ('2017-08-03',567, 780, 432);
  INSERT INTO article_views VALUES ('2017-08-01',789, 457    ,789);
     
  date        viewer_id        article_id      author_id
  2017-08-01 123   456   789
  2017-08-02 432   543   654
  2017-08-01 789   456   789
  2017-08-03 567   780   432
  2017-08-01 789   457   789 

SQL question challenge (Ads conversion)

Link to Github
Here we have 2 tables

adv_info: advertiser_id | ad_id | spend: The Advertiser pay for this ad

ad_info: ad_id | user_id | price: The user spend through this ad (Assume all prices in this column >0)

DDL Scripts

  CREATE TABLE adv_info (
  advertiser_id    int,
  ad_id    int,
  spend    float
 );

 INSERT INTO adv_info VALUES (10, 200,300);
 INSERT INTO adv_info VALUES (11, 100,1000);
 INSERT INTO adv_info VALUES (13, 400,3000);
 INSERT INTO adv_info VALUES (14, 500,5000);

 CREATE TABLE ad_info (
  ad_id    int,
  user_id    int,
  price    float
 );

 INSERT INTO ad_info VALUES (200, 4,30);
 INSERT INTO ad_info VALUES (100, 4,100);
 INSERT INTO ad_info VALUES (400, 4,300);
 INSERT INTO ad_info VALUES (200, 10,31);
 INSERT INTO ad_info VALUES (100, 10,110);
 INSERT INTO ad_info VALUES (400, 10,310);

 select * 
  from ad_info;
  +-------+---------+-------+
  | ad_id | user_id | price |
  +-------+---------+-------+
  |   200 |       4 |    30 |
  |   100 |       4 |   100 |
  |   400 |       4 |   300 |
  |   200 |      10 |    31 |
  |   100 |      10 |   110 |
  |   400 |      10 |   310 |
  +-------+---------+-------+
  
 select * 
  from adv_info;
  +---------------+-------+-------+
  | advertiser_id | ad_id | spend |
  +---------------+-------+-------+
  |            10 |   200 |   300 |
  |            11 |   100 |  1000 |
  |            13 |   400 |  3000 |
  |            14 |   500 |  5000 |
  +---------------+-------+-------+