SQL practice
Courses Above Their Department's Average
medium Original problem by Avio Lines
Each row is one student's marks in one course. A course stands out when its average marks are strictly higher than the average of all marks in its department.
Return dept, course_code and course_avg (rounded to 2 decimal places) for every course that stands out, ordered by dept, then course_code.
Row order matters for this problem. Column names don't have to match.
Same idea on LeetCode
- 615. Average Salary: Departments VS Company Premiumvideo by Everyday Data Science
- 1164. Product Price at a Given Datevideo 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 |