#028Level 5 of 7 · Scenario 5: The Gate Book
The monthly return
Scenario 5 · The Gate Book
The monthly return goes to the board, and the person who used to build it by filtering the gate book one month at a time has left. Three numbers per month: how many bookings, how many people through the door, and what was taken.
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.
- DATE_TRUNC
- Flatten a timestamp to the start of a period: DATE_TRUNC('month', visited_at) turns any day in March into 1 March.
- ROUND
- Round a number to n decimal places.
Objective
Return one row per month with the month, the number of bookings, the total number of people and the total takings to two decimal places. Chronological order.
Your query
⌘ ⏎
Results
No result yet.