SQL Challenges-1: Rising Sulfur Dioxide

SQL Challenges-1: Exercise-8 with Solution

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:

Data:

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.