SQL Challenges-1: Writers who rated more than one topics on the same date

Last update on December 20 2024 11:39:32 (UTC/GMT +8 hours)

From the following table write a SQL query to find all the writers who rated more than one topics on the same date, sorted in ascending order by their id. Return writr ID.

Input:

Table: topics

Structure:

Field	Type	Null
topic_id	int(11)	YES
writer_id	int(11)	YES
rated_by	int(11)	YES
date_of_rating	date	YES

Data:

topic_id	writer_id	rated_by	date_of_rating
10001	504	507	2020-07-17
10003	502	503	2020-09-22
10001	503	507	2020-12-23
10002	501	507	2020-07-17
10002	502	502	2020-04-10
10002	504	502	2020-11-16
10003	501	502	2020-04-10
10001	507	507	2020-12-23
10004	503	501	2020-08-28
10003	505	504	2020-12-21

Sample Solution:

SQL Code(MySQL):

CREATE TABLE topics (topic_id int, writer_id int, rated_by int, date_of_rating date);
INSERT INTO topics VALUES (10001,504,507,'2020-07-17');
INSERT INTO topics VALUES (10003,502,503,'2020-09-22'); 
INSERT INTO topics VALUES (10001,503,507,'2020-12-23'); 
INSERT INTO topics VALUES (10002,501,507,'2020-07-17'); 
INSERT INTO topics VALUES (10002,502,502,'2020-04-10'); 
INSERT INTO topics VALUES (10002,504,502,'2020-11-16'); 
INSERT INTO topics VALUES (10003,501,502,'2020-04-10'); 
INSERT INTO topics VALUES (10001,507,507,'2020-10-23'); 
INSERT INTO topics VALUES (10004,503,501,'2020-08-28'); 
INSERT INTO topics VALUES (10003,505,504,'2020-12-21'); 


SELECT DISTINCT rated_by AS 'Topic rated by the writer' 
FROM topics
GROUP BY rated_by, date_of_rating 
HAVING COUNT(DISTINCT topic_id) > 1
ORDER BY 1

Sample Output:

Topic rated by the writer|
-------------------------|
                      502|
                      507|

SQL Code Editor:

Contribute your code and comments through Disqus.

Previous: Find all the writers who rated at least one of their own topic.
Next: Sale quantity of each quarter for a product.