SQL Challenges-1: Rising Sulfur Dioxide

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

From the following table, write a SQL query to find all dates' city ID with higher pollution compared to its previous dates (yesterday). Return city ID, date and pollution.

Input:

Table: so2_pollution

Structure:

Field	Type	Null
city_id	int(11)	YES
date	date	YES
so2_amt	int(11)	YES

Data:

city_id	date	so2_amt
701	2015-10-15	5
702	2015-10-16	7
703	2015-10-17	9
704	2018-10-18	15
105	2015-10-19	14

Sample Solution:

SQL Code:

CREATE TABLE IF NOT EXISTS so2_pollution (city_id int, date date, so2_amt int);
TRUNCATE TABLE so2_pollution;
INSERT INTO so2_pollution (city_id, date, so2_amt) VALUES ('701', '2015-10-15', '5');
INSERT INTO so2_pollution (city_id, date, so2_amt) VALUES ('702', '2015-10-16', '7');
INSERT INTO so2_pollution (city_id, date, so2_amt) VALUES ('703', '2015-10-17', '9');
INSERT INTO so2_pollution (city_id, date, so2_amt) VALUES ('704', '2018-10-18', '15');
INSERT INTO so2_pollution (city_id, date, so2_amt) VALUES ('705', '2015-10-19', '14');
SELECT * FROM so2_pollution;
 
SELECT so2_pollution.city_id AS 'City ID'
FROM so2_pollution
        JOIN
so2_pollution p ON DATEDIFF(so2_pollution.date, p.date) = 1
        AND so2_pollution.so2_amt > p.so2_amt;

Sample Output:

City ID|
-------|
    702|
    703|

Solution-1:

SELECT p2.city_id FROM so2_pollution p1, so2_pollution p2
WHERE p2.date = adddate(p1.date,1)
AND p1.so2_amt < p2.so2_amt

SQL Code Editor:

Contribute your code and comments through Disqus.

Previous: Remove Duplicate Emails.
Next: Highest Sale Amount.