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

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

## SQL Challenges-1: Exercise-40 with Solution

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:

FieldTypeNullKeyDefaultExtra
topic_idint(11)YES
writer_idint(11)YES
rated_byint(11)YES
date_of_ratingdateYES

Data:

topic_idwriter_idrated_bydate_of_rating
100015045072020-07-17
100035025032020-09-22
10001503507 2020-12-23
10002501507 2020-07-17
100025025022020-04-10
100025045022020-11-16
10003501502 2020-04-10
10001507507 2020-12-23
100045035012020-08-28
100035055042020-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:

﻿

## SQL: Tips of the Day

How to select the nth row in a SQL database table?

Basically, PostgreSQL and MySQL supports the non-standard:

```SELECT...
LIMIT y OFFSET x
```

Oracle, DB2 and MSSQL supports the standard windowing functions:

```SELECT * FROM (
SELECT
ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
columns
FROM tablename
) AS foo
WHERE rownumber <= n
```

Database: SQL

Ref: https://bit.ly/3zPxcD8