Sql : Leet code Problem Solved : Category: Easy : Write an SQL query to find all the authors that viewed at least one of their own articles, sorted in ascending order by their id: Problem 3
In Continuation of my earlier article on sql this one is problem 3.
This problem is really easy and may not need much of an explanation but for covering our problem area I do not want to skip any problem.
As usual we will take up below 4 steps one after another.
Section 1: Talks about the Problem
Section 2: Prepare your data
Section 3: Analyse the Problem and find the most optimized solution
Section 4: Write the sql query for the problem
So let us start to have fun.
Section 1: Talks about the Problem
— Table: Views
— + — — — — — — — -+ — — — — -+
— | Column Name | Type |
— + — — — — — — — -+ — — — — -+
— | article_id | int |
— | author_id | int |
— | viewer_id | int |
— | view_date | date |
— + — — — — — — — -+ — — — — -+
— There is no primary key for this table, it may have duplicate rows.
— Each row of this table indicates that some viewer viewed an article (written by some author) on some date.
— Note that equal author_id and viewer_id indicate the same person.
— Write an SQL query to find all the authors that viewed at least one of their own articles, sorted in ascending order by their id.
— The query result format is in the following example:
— Views table:
— + — — — — — — + — — — — — -+ — — — — — -+ — — — — — — +
— | article_id | author_id | viewer_id | view_date |
— + — — — — — — + — — — — — -+ — — — — — -+ — — — — — — +
— | 1 | 3 | 5 | 2019–08–01 |
— | 1 | 3 | 6 | 2019–08–02 |
— | 2 | 7 | 7 | 2019–08–01 |
— | 2 | 7 | 6 | 2019–08–02 |
— | 4 | 7 | 1 | 2019–07–22 |
— | 3 | 4 | 4 | 2019–07–21 |
— | 3 | 4 | 4 | 2019–07–21 |
— + — — — — — — + — — — — — -+ — — — — — -+ — — — — — — +
— Result table:
— + — — — +
— | id |
— + — — — +
— | 4 |
— | 7 |
— + — — — +
Section 2: Prepare your data
Let us prepare our data.
Create table
create table article_views
(
article_id int,
author_id int,
viewer_id int,
view_date date
)
insert table
insert into article_views
values
(1,3,5,’2019–08–01'),
(1,3,6,’2019–08–02'),
(2,7,7,’2019–08–01'),
(2,7,6,’2019–08–02'),
(4,7,1,’2019–07–22'),
(3,4,4,’2019–07–21'),
(3,4,4,’2019–07–21')
Section 3: Analyse the Problem and find the most optimized solution
Let us have a quick look on the data.
Ok. So, this is a very simple problem.
We have to find authors who have viewed their own article atleast once.
So, in sql query we just have to check if the author_id is present as viewer_id also and we have to just select those author_id(s) who are present as viewer_id also.
This is kind of self-join problem.
Hope I am able to explain the simple problem in simple manner.
As data engineer and data architect our main passion is to simplify and break down complex problems so that we are able to provide the better solution every time.
Section 4: Write the sql query for the problem
select distinct author_id
from article_views
where author_id = viewer_id
Hope you have enjoyed the article.
Happy Coding!! Happy Reading!!
Please refer the below github url for code download and your own practice.
sql_leetcode_solved/Easy at main · sanjitkhasnobis/sql_leetcode_solved (github.com)
