Link to github
DDL Scripts
QUESTIONS
1)HOW MANY ARTICLES AUTHORS HAVE NEVER VIEWED THEIR OWN ARTICLE?
2)HOW MANY MEMBERS VIEWED MORE THAN ONE ARTICLES ON 2017-08-01
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
QUESTIONS
1)HOW MANY ARTICLES AUTHORS HAVE NEVER VIEWED THEIR OWN ARTICLE?
select vw.viewer_id,au.author_id from article_views vw left outer join article_views au on vw.viewer_id=vw.author_id where au.author_id isnull; +-----------+-----------+ | viewer_id | author_id | +-----------+-----------+ | 123 | NULL | | 432 | NULL | | 567 | NULL | +-----------+-----------+
2)HOW MANY MEMBERS VIEWED MORE THAN ONE ARTICLES ON 2017-08-01
select viewer_id from article_views where date =TIMESTAMP('2017-08-01') group by viewer_id having count(viewer_id) > 1; +-----------+ | viewer_id | +-----------+ | 789 | +-----------+
No comments:
Post a Comment