SQL practice
Out-Earning the Team Lead
medium Original problem by Avio Lines
The payroll team is auditing pay. Find every staff member who earns strictly more than their own team lead.
Return name, the lead's name as lead_name, and pay_gap (staff pay minus lead pay). Order by pay_gap, largest first, then by name.
Staff with no team lead (team_lead_id is NULL) are left out. So is anyone whose lead isn't in the table.
Row order matters for this problem. Column names don't have to match.
Same idea on LeetCode
- 181. Employees Earning More Than Their Managersvideo 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 |