Model course sales for a sports venue chain, query it, and track manager history

A chain of sports venues sells classes at each of its venues, and the operations team wants to see sales by venue and by class. You receive three source files. Design how they are modeled in the data warehouse, then write SQL against your own design. - **Venues (dimension).** One row per venue: venue id, short name, address and the id of the venue's manager. Every delivery holds only the latest state: if a venue changed manager last week, this week's file shows only the new manager's id. - **Class catalog (dimension).** One row per class: class id, class name, and the ids of its category and of its subcategory. For example, the class "Weekend Rock Climbing Intro" has the category "Sports classes" and the subcategory "Climbing". This file is also current-version only. - **Checkouts (fact).** One record per checkout, shaped like a receipt. The outer level holds the transaction id, the member id, the venue where the purchase was made and the payment time. Inside is a list of line items: a checkout that buys a climbing class and a swimming class has two line items, each with its line number within the checkout, the class id and the amount actually paid. Every record references the venue and class files. The round is on a whiteboard and nothing is executed. The interviewer wants to see your reasoning, so write your plan down before you write a query. ### Clarifying Questions - Does "sold best" mean the most line items sold or the highest revenue? - Can a line item be refunded, discounted to zero or reversed, and should reports net those out? - Which time zone defines "a day": the venue's local time or one company-wide zone? - When several classes tie, should a query return all of them or break the tie? - How often do the files arrive, how large are they, and can a checkout be redelivered with corrections? - Where do category and subcategory names come from, given that the catalog carries only their ids? ### Part 1 — Design the warehouse tables Explain how the three sources are split into warehouse tables. For each table, state what one row represents (its grain), its primary key, its foreign keys, its NOT NULL and uniqueness rules, and how the tables join. Then say which business questions the model can answer, why you chose it, which trade-offs you made and how it scales. ```hint Find the grain in the receipt Decide what one row of your fact table stands for when a single checkout buys several classes, and check what would break if you kept one row per checkout. ``` #### What This Part Should Cover - The declared grain of every table, with the fact table at the finest grain the questions need - Keys and constraints: primary keys, foreign keys, nullability, natural versus surrogate keys - How the category and subcategory hierarchy is represented and joined - The business questions supported, the trade-offs taken and how the tables scale ### Part 2 — Query your design Write SQL against your tables for each of these: 1. For a given time range, which class sold best? 2. For a given day, which classes earned more than a given revenue threshold? 3. Within each subcategory, which class has the highest revenue, ranked by the amount actually paid? 4. Counting from the first transaction on record, which manager has the highest total revenue? ```hint Aggregate before you rank For the per-subcategory question, decide which totals must exist before any ranking can happen, and what should happen when two classes have the same total. ``` #### What This Part Should Cover - Aggregation at the line-item grain without double counting - Time filters that are correct at their boundaries and can use partitioning - A ranking step with an explicit tie rule - What query 4 actually returns, given that the venue file holds only the current manager ### Part 3 — Manager changes Suppose a venue changed manager partway through. Revenue from before the change must still be credited to the previous manager, but the venue file contains only the current one. How do you change your tables and your load, and how does query 4 change? ```hint Snapshots forget The source never says when a manager changed; think about what your load can record each time a new snapshot differs from the previous one. ``` #### What This Part Should Cover - A history-preserving venue table with validity ranges and a current-row flag - How the load detects a change and closes the old version - How each sale is matched to the version valid at its payment time - The limits of the approach: change dates known only to snapshot precision, and history from before tracking began ### What a Strong Answer Covers - The grain stated first and used consistently in the tables and the queries - Keys, constraints and joins that make double counting and orphan rows detectable - Queries that are correct for ties, time boundaries and classes with no sales - Dimension history handled where a report needs it, weighed against simplicity - Scaling through partitioning, incremental idempotent loads and pre-aggregated tables - Reasoning written down before code, as the whiteboard format expects ### Follow-up Questions - A class moves to a different subcategory. Which reports should show the old subcategory and which the new one? - A checkout is redelivered with a corrected amount. How does your load avoid counting it twice? - How would you report revenue per member across venues, and what changes in the model? - After several years of history, how do you keep query 4 fast?