Cities where cases rose every day
SQL data engineering interview problem. Difficulty: intermediate. Pattern: Window Functions. About 16 minutes. Part of the Pro drill bank.
city_daily_cases records epidemic data with columns city, report_date, and confirmed_cases. Find all cities where the daily case count strictly increased on every single recorded day throughout the monitoring period (confirmed_cases on day t > confirmed_cases on day t-1 for all days after the first). The city must have at least 2 recorded days. Return city, ordered by city ascending.
Requirements
- Output column: city.
- Order by city ascending.
Constraints
- confirmed_cases >= 0.
- Cities must have at least 2 days of recordings.
Examples
Input: city_daily_cases city,report_date,confirmed_cases Bengaluru,2024-08-01,100 Bengaluru,2024-08-02,150 Bengaluru,2024-08-03,200 Mumbai,2024-08-01,300 Mumbai,2024-08-02,280 Mumbai,2024-08-03,350 Delhi,2024-08-01,400 ... Output: city Bengaluru Why this passes: Bengaluru rises 100 -> 150 -> 200 every day; Mumbai dips on day 2 and Delhi is flat between days 2 and 3, so only Bengaluru qualifies.
Input: city_daily_cases city | report_date | confirmed_cases Pune | 2024-08-01 | 10 Pune | 2024-08-02 | 20 Pune | 2024-08-03 | 30 Output: city Pune Why this passes: 10 -> 20 -> 30 strictly increases each day.
Topics: lakebench, sql, monotonic, rank.
More SQL interview questions · All interview problems · Learn data engineering
Cities where cases rose every day
Interview-style drill: Filter for cities whose daily case count strictly increased on every single recorded day.
`city_daily_cases` records epidemic data with columns `city`, `report_date`, and `confirmed_cases`. Find all cities where the daily case count strictly increased on every single recorded day throughout the monitoring period (`confirmed_cases` on day t > `confirmed_cases` on day t-1 for all days after the first). The city must have at least 2 recorded days. Return `city`, ordered by `city` ascending.