AccessionSQL, one case at a time
#026Level 3 of 7 · Scenario 5: The Gate Book

Per head

Scenario 5 · The Gate Book

Admission is 18.00 standard, 12.00 concession and a flat 48.00 for a family, and every transaction carries a 2.50 booking fee on top. Finance is being asked what the museum actually takes per visitor and nobody can answer, because the fee means no two bookings of the same size cost the same per person.

Schema · 1 table
visits
  • idpkINTEGER
  • visitor_nameVARCHAR
  • home_townVARCHAR
  • ticket_typeVARCHAR
  • priceDOUBLE
  • party_sizeINTEGER
  • visited_atTIMESTAMP

Toolkit

SELECT
Choose which columns come back.
FROM
Specify which table to read.
WHERE
Filter rows by a condition.
ORDER BY
Sort the results by a column.
DESC
Reverse the sort: largest or latest first.
COUNT(*)
How many rows there are.
SUM
Add up a numeric column.
GROUP BY
Split the rows into groups first, then aggregate each one.
TRIM
Strip whitespace from both ends.
ROUND
Round a number: ROUND(value, 2) keeps two decimal places.
/
Divide. Also + - * , which behave as you would expect.
Objective

For every booking that paid something, return the visitor's name with its spaces stripped, the party size, and what the booking worked out at per head to two decimal places. Most expensive per head first.

Your query

starting DuckDB…
loading editor…

Results

No result yet.