Google: Pixel Units Sold and Returned per Model

Easy
678.

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.

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

ColumnTypeValues
line_idint
order_idint
modelvarchar
unitsint
line_typeenumsale, 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.

Example

The rows must come back in the order shown.

PixelOrderLine

line_idorder_idmodelunitsline_type
15001Pixel 92sale
25002Pixel 9 Pro1sale
35003Pixel 91sale
45001Pixel 91return
55004Pixel 9a3sale
65005Pixel 9 Pro1sale
75005Pixel 9 Pro1return
85006Pixel 9 Pro Fold1sale

Output

modelunits_soldunits_returnednet_units
Pixel 9a303
Pixel 9312
Pixel 9 Pro211
Pixel 9 Pro Fold101
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
line_idint
order_idint
modelvarchar
unitsint
line_typeenum(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_idorder_idmodelunitsline_type
15001Pixel 92sale
25002Pixel 9 Pro1sale
35003Pixel 91sale
45001Pixel 91return
55004Pixel 9a3sale
65005Pixel 9 Pro1sale
75005Pixel 9 Pro1return
85006Pixel 9 Pro Fold1sale

Output

modelunits_soldunits_returnednet_units
Pixel 9a303
Pixel 9312
Pixel 9 Pro211
Pixel 9 Pro Fold101

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, model

Another 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, model

Another 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 →