Driver Earnings Summary
For each driver, what are their total gross earnings, net earnings after commission, and tips received?
- Joins
- Aggregation
- Numeric functions
- NULL handling
- Sorting
Challenge brief
Understand the request
Driver Payments is preparing the weekly pay-out report and needs gross earnings, net earnings, and tips per driver.
Summarise driver_earnings per driver showing gross, net, and tip totals — include drivers with no earnings.
Return
- driver_name (full name)
- rating
- trips_with_earnings
- gross_earnings (rounded 2)
- net_earnings (rounded 2)
- total_tips (rounded 2)
Constraints
- Return every driver, including drivers with no earnings records
- Treat missing monetary totals as zero
- Count matched earnings records rather than preserved driver rows
- Order by net earnings descending, then driver ID
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
drivers
driver_idINTEGERfirst_nameVARCHAR(50)last_nameVARCHAR(50)ratingREAL
driver_earnings
earning_idINTEGERdriver_idINTEGERtotal_earningREALnet_earningREALtip_amountREAL
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
Driver names are in drivers. Earnings breakdown is in driver_earnings. LEFT JOIN on driver_id so David Brown (no earnings yet) still appears. COALESCE converts his NULLs to 0.
Hint 2
LEFT JOIN drivers to driver_earnings on driver_id. GROUP BY driver. COUNT(de.earning_id) for trip count. COALESCE(SUM(total_earning), 0), COALESCE(SUM(net_earning), 0), COALESCE(SUM(tip_amount), 0). ORDER BY net_earnings DESC.
Hint 3
Preserve the driver dimension with an optional earnings match; aggregate matched fact identifiers and zero-fill missing monetary sums.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT d.first_name || ' ' || d.last_name AS driver_name, d.rating, COUNT(de.earning_id) AS trips_with_earnings, ROUND(COALESCE(SUM(de.total_earning), 0), 2) AS gross_earnings, ROUND(COALESCE(SUM(de.net_earning), 0), 2) AS net_earnings, ROUND(COALESCE(SUM(de.tip_amount), 0), 2) AS total_tips FROM drivers d LEFT JOIN driver_earnings de ON d.driver_id = de.driver_id GROUP BY d.driver_id, d.first_name, d.last_name, d.rating ORDER BY net_earnings DESC, d.driver_idWhy this works
LEFT JOIN keeps David Brown even though he has no earnings record — his earning_id is NULL, making COUNT = 0 and all COALESCE sums = 0. Sarah Williams earns the most net ($55.50) despite a lower fare — she had a $8 tip on a surge trip.
Success check
5 drivers; Sarah leads net earnings, John has two earning records, and David has zero-filled totals
Expected result
Use this output to verify values, aliases, ordering, and row count.
| driver_name | rating | trips_with_earnings | gross_earnings | net_earnings | total_tips |
|---|---|---|---|---|---|
| Sarah Williams | 4.6 | 1 | 74 | 55.5 | 8 |
| Jane Smith | 4.9 | 1 | 65 | 48.75 | 5 |
| John Doe | 4.8 | 2 | 45.5 | 34.13 | 5 |
| Mike Johnson | 4.7 | 1 | 32 | 24 | 4 |
| David Brown | 4.5 | 0 | 0 | 0 | 0 |
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Explore related company challenges
Airbnb
Independent Airbnb-style marketplace, booking, listing, payment, review, and guest analytics SQL practice.
Amazon
Independent Amazon-style e-commerce, warehouse, inventory, and customer analytics SQL practice.
Google
Independent Google-style search, advertising, user-engagement, and video-product SQL practice.
Return to the complete interview preparation experience.