#027Level 4 of 7 · Scenario 5: The Gate Book
Before eleven
Scenario 5 · The Gate Book
The museum opens at nine and the first tour leaves at eleven, and front of house wants to stop staffing the early slot. Before they do, the duty manager wants to know who is actually coming through the door in those two hours.
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.
- EXTRACT
- Pull one part out of a date or timestamp: EXTRACT(year FROM visited_at). Also month, day, hour, minute, dow, quarter.
Objective
Return every booking that arrived before eleven in the morning, with the visitor's name tidied, the month number and the hour it arrived. Earliest visit first.
Your query
⌘ ⏎
Results
No result yet.