SQL practice
Grade Bands by Department
easy Original problem by Avio Lines
A college publishes how many students in each department landed in each grade band:
| Band | Marks |
|---|---|
| A | 85 and above |
| B | 70 to 84 |
| C | 50 to 69 |
| F | below 50 |
Students who were absent have marks = NULL. Count them separately.
Return one row per department with the columns dept, a_count, b_count, c_count, f_count and absent_count, ordered by dept. A band with no students shows 0.
Row order matters for this problem. Column names don't have to match.
Same idea on LeetCode
- 1873. Calculate Special Bonusvideo by Everyday Data Science
- 1907. Count Salary Categoriesvideo 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 |