Google: Pixel Units Sold and Returned per Model
The Google Store's hardware planning team reconciles sales against returns for each Pixel model before the next production order.
- Difficulty: Easy
- Topics: Aggregation, Conditional Logic
- Dialect: MySQL
- Problem: #678
Problem statement
The Google Store's hardware planning team reconciles sales against returns for each Pixel model before the next production order.
For every model with at least one order line, return model, units_sold (the units of its sale lines), units_returned (the units of its return lines) and net_units (sold minus returned). A model with no returns shows 0 returned. Order by net_units descending, then model ascending.
A practice scenario set at Google: the tables and data are invented, and this is not a question from, or endorsed by, Google.
Tables
Table: PixelOrderLine
| Column | Type |
|---|---|
| line_id | int |
| order_id | int |
| model | varchar |
| units | int |
| line_type | enum(sale, return) |
Primary key: line_id.
One line of a Google Store order. A return is logged as its own line with line_type = 'return' and a positive units.
Examples
Example 1
PixelOrderLine
| line_id | order_id | model | units | line_type |
|---|---|---|---|---|
| 1 | 5001 | Pixel 9 | 2 | sale |
| 2 | 5002 | Pixel 9 Pro | 1 | sale |
| 3 | 5003 | Pixel 9 | 1 | sale |
| 4 | 5001 | Pixel 9 | 1 | return |
| 5 | 5004 | Pixel 9a | 3 | sale |
| 6 | 5005 | Pixel 9 Pro | 1 | sale |
| 7 | 5005 | Pixel 9 Pro | 1 | return |
| 8 | 5006 | Pixel 9 Pro Fold | 1 | sale |
Output
| model | units_sold | units_returned | net_units |
|---|---|---|---|
| Pixel 9a | 3 | 0 | 3 |
| Pixel 9 | 3 | 1 | 2 |
| Pixel 9 Pro | 2 | 1 | 1 |
| Pixel 9 Pro Fold | 1 | 0 | 1 |
How to solve Google: Pixel Units Sold and Returned per Model
All three figures come from the same group of lines, so one GROUP BY model and conditional aggregation answer it in a single pass. SUM(CASE WHEN line_type = 'sale' THEN units ELSE 0 END) adds only the sale units; the same with 'return' adds the returned units. For the net, the CASE can return -units for a return so the sum subtracts it directly.
The ELSE 0 matters: it makes a model with no returns report 0 rather than NULL, and keeps a model whose only lines are returns (possible when the sale happened before the period) correct with 0 sold and a negative net.
Splitting into two grouped subqueries — one for sales, one for returns — joined back to the list of models gives the same result with more reading. The order is fully fixed: net units descending, then the model name.
Reference solution (MySQL)
SELECT model,
SUM(CASE WHEN line_type = 'sale' THEN units ELSE 0 END) AS units_sold,
SUM(CASE WHEN line_type = 'return' THEN units ELSE 0 END) AS units_returned,
SUM(CASE WHEN line_type = 'sale' THEN units ELSE -units END) AS net_units
FROM PixelOrderLine
GROUP BY model
ORDER BY net_units DESC, modelAnother way
SELECT model, SUM(IF(line_type = 'sale', units, 0)) AS units_sold, SUM(IF(line_type = 'return', units, 0)) AS units_returned, SUM(IF(line_type = 'sale', units, 0)) - SUM(IF(line_type = 'return', units, 0)) AS net_units FROM PixelOrderLine GROUP BY model ORDER BY net_units DESC, modelAnother way
SELECT m.model, COALESCE(s.u, 0) AS units_sold, COALESCE(r.u, 0) AS units_returned, COALESCE(s.u, 0) - COALESCE(r.u, 0) AS net_units FROM (SELECT DISTINCT model FROM PixelOrderLine) m LEFT JOIN (SELECT model, SUM(units) AS u FROM PixelOrderLine WHERE line_type = 'sale' GROUP BY model) s ON s.model = m.model LEFT JOIN (SELECT model, SUM(units) AS u FROM PixelOrderLine WHERE line_type = 'return' GROUP BY model) r ON r.model = m.model ORDER BY net_units DESC, m.model← Google: March Cloud Bill for Every Project · Google: Read Match Type From Ad Keyword Syntax →