SQL practice
Weekend Late Deliveries
medium Original problem by Avio Lines
An order counts as late if it was delivered more than 45 minutes after it was placed. Exactly 45 minutes is on time.
For orders placed on a Saturday or Sunday that were delivered (delivered_at is not NULL), report per zone:
weekend_deliveries: the number of such orderslate_deliveries: how many of them were latelate_pct: late as a percentage of weekend deliveries, rounded to 1 decimal place
Include only zones with at least one weekend delivery, ordered by zone.
Row order matters for this problem. Column names don't have to match.
Same idea on LeetCode
- 2298. Tasks Count in the Weekend Premiumvideo by Everyday Data Science
- 1479. Sales by Day of the Week Premiumvideo by Everyday Data Science
PostgreSQL starting the database in your browser…
Loading editor…
Run tries your query on the sample data shown in Tables. Submit judges it on 4 hidden datasets with edge cases.
Coming from MySQL (LeetCode)? Postgres differences
| MySQL | PostgreSQL |
|---|---|
| IFNULL(a, b) | COALESCE(a, b)COALESCE works in both. |
| DATE_FORMAT(d, '%Y-%m') | TO_CHAR(d, 'YYYY-MM') |
| DATEDIFF(a, b) | a - bfor DATE values; gives days |
| DATE_ADD(d, INTERVAL 7 DAY) | d + INTERVAL '7 days' |
| DAYOFWEEK(d) (Sunday = 1) | EXTRACT(ISODOW FROM d) (Monday = 1, Sunday = 7) |
| GROUP_CONCAT(x ORDER BY x) | STRING_AGG(x, ',' ORDER BY x) |
| LIMIT 1, 2 | LIMIT 2 OFFSET 1 |
| `backticks` | "double quotes"for identifiers; 'single quotes' are strings in both |
| IF(cond, a, b) | CASE WHEN cond THEN a ELSE b END |
| 5 / 2 = 2.5 | 5 / 2 = 2integer division: write 5.0 / 2 or CAST(x AS NUMERIC) |
| ORDER BY x DESC (NULLs last) | ORDER BY x DESC NULLS LASTPostgres treats NULL as the largest value |
| GREATEST(NULL, 1) = NULL | GREATEST(NULL, 1) = 1Postgres skips NULLs in GREATEST/LEAST |