Predict: payments after joining line items

Predict line_rows and paid_total for the two sample orders when items and payments are joined together.

what to return

Columns: order_idline_rowspaid_total in this order
Rows: in the order asked
Watch for: Use only the sample rows shown. NULL is not zero or empty text. Keep duplicate rows.
Skill: Reason about the exact result of a fixed SQL query. Refresher: Multi-grain joins without fan-out →

data you can query

Retail dataset: the first 6 rows of each table. Under each column name: the kind of value it holds, and which table it links to. Your queries run on all rows.

order_sample2 columns · 2 rows

order_id	invoice_no
211	492125
275	493071

order_id: 211
invoice_no: 492125

order_id: 275
invoice_no: 493071

all 2 rows

item_sample4 columns · 5 rows

order_id	stock_code	quantity	unit_price
211	84598	288	0.21
211	21407	6	4.25
211	84568	288	0.21
275	21479	18	3.75
275	22114	96	3.45

order_id: 211
stock_code: 84598
quantity: 288
unit_price: 0.21

order_id: 211
stock_code: 21407
quantity: 6
unit_price: 4.25

order_id: 211
stock_code: 84568
quantity: 288
unit_price: 0.21

order_id: 275
stock_code: 21479
quantity: 18
unit_price: 3.75

order_id: 275
stock_code: 22114
quantity: 96
unit_price: 3.45

all 5 rows

payment_sample3 columns · 4 rows

payment_id	order_id	amount
136	211	43.94
233	211	102.52
212	275	398.7
217	275	398.7

payment_id: 136
order_id: 211
amount: 43.94

payment_id: 233
order_id: 211
amount: 102.52

payment_id: 212
order_id: 275
amount: 398.7

payment_id: 217
order_id: 275
amount: 398.7

all 4 rows

predict.sql

SELECT
  o.order_id,
  COUNT(*) AS line_rows,
  ROUND(SUM(p.amount), 2) AS paid_total
FROM order_sample o
JOIN item_sample i ON i.order_id = o.order_id
JOIN payment_sample p ON p.order_id = o.order_id
GROUP BY o.order_id
ORDER BY o.order_id

Read the query · predict its result

SELECT o.order_id, COUNT(*) AS line_rows, ROUND(SUM(p.amount), 2) AS paid_total FROM order_sample o JOIN item_sample i ON i.order_id = o.order_id JOIN payment_sample p ON p.order_id = o.order_id GROUP BY o.order_id ORDER BY o.order_id

Look at the sample tables and SQL. Enter the rows you think the query returns, in this column order: order_id, line_rows, paid_total. Put each row in square brackets, then wrap all rows in another pair of brackets (JSON format). For example, [[1,null,null]] is one row with 3 values. Replace these with your predicted values. Use numbers without quotes, text in double quotes, and null for SQL NULL. Keep repeated rows. Use [] for no rows. Keep rows in the order specified by ORDER BY.

Your predicted rows

Showing the result or using a hint means a correct prediction counts as solved with help. Check prediction tests your answer without showing the result.

Hints and the answer are always available. Using them counts the question as solved with help.