SQL practice
Cap Overtime Claims
easy Original problem by Avio LinesModifies data
A new policy caps overtime at 12 hours per week. Some entries are also negative because of a data-entry bug. Fix the table in place with a single UPDATE:
- above 12 → set to 12
- below 0 → set to 0
- everything else, including NULL (not yet filed), stays as it is
Don't delete any rows. The judge checks the whole table after your statement runs.
Row order matters for this problem. Column names don't have to match.
Same idea on LeetCode
- 627. Swap Sex of Employeesvideo 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 |