Write SQL for theme-park revenue and visits
Company: Capital One
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
You are given theme-park ticketing and visits data. Write SQL to answer the following, using the sample schema and tables below. Return both the query and the final numeric answers. Edge cases matter: a user may hold multiple ticket types over time; for (c) count only visits within each holder’s active annual-pass period; include holders with zero qualifying visits in the average.
Schema and sample data:
Table: ticket_types
id | name | price_usd | duration_days
---+--------------+-----------+---------------
1 | single_day | 80 | 1
2 | five_day | 300 | 5
3 | annual_pass | 1000 | 365
Table: ticket_sales -- each row is an order
order_id | user_id | ticket_type_id | quantity | order_date
---------+---------+----------------+----------+------------
101 | 1 | 3 | 1 | 2024-03-15
102 | 2 | 1 | 2 | 2024-06-01
103 | 3 | 2 | 1 | 2024-07-10
104 | 4 | 1 | 1 | 2024-08-20
105 | 5 | 3 | 1 | 2024-08-21
Table: ticket_sales_summary -- pre-aggregated units sold in the last fiscal year
ticket_type_id | units_sold
---------------+------------
1 | 250000
2 | 100000
3 | 10000
Table: annual_pass_periods -- one row per user per pass
user_id | start_date | end_date
--------+------------+----------
1 | 2024-03-15 | 2025-03-14
5 | 2024-08-21 | 2025-08-20
Table: visits -- park entries; a user may have multiple visits
user_id | visit_date
--------+-----------
1 | 2024-06-10
1 | 2024-06-11
1 | 2024-07-01
5 | 2024-09-01
5 | 2024-09-15
2 | 2024-06-15
Tasks:
(a) Compute total revenue and revenue share by ticket type using ticket_sales_summary × ticket_types.
(b) Derive total annual entries implied by sales assuming: single_day yields 1 entry per unit; five_day yields 5 entries per unit; annual_pass yields an unknown average entries per holder, call it X. Express total entries as a function of X.
(c) Using annual_pass_periods and visits, compute the empirical average number of visits per annual-pass holder in their active period (count unique visits per holder within [start_date, end_date], average across holders; include holders with zero visits; exclude visits outside the holder’s active period; if a user bought multiple passes, treat each pass period separately).
(d) Replace X in (b) with your result from (c) and recompute total entries.
Overview: This question evaluates SQL data-manipulation and analytical skills, including joins, aggregations, date-range filtering, handling multiple ticket ownership, and edge cases like zero counts when computing revenue and visit metrics.
Read the full Capital One Data Scientist interview experience this question came from
Theme-park revenue and revenue share by ticket type
Using the tables below, compute total revenue by ticket type over the last fiscal year using ticket_sales_summary × ticket_types. For each ticket type, return:
- ticket_type_id
- name
- revenue_usd = units_sold * price_usd
- revenue_share = revenue_usd divided by total revenue across all ticket types
Return one row per ticket type.
Tables
ticket_types(id INT, name VARCHAR(50), price_usd DECIMAL(10,2), duration_days INT)
ticket_sales(order_id INT, user_id INT, ticket_type_id INT, quantity INT, order_date DATE)
ticket_sales_summary(ticket_type_id INT, units_sold INT)
annual_pass_periods(user_id INT, start_date DATE, end_date DATE)
visits(user_id INT, visit_date DATE)
Hints
- Join ticket_sales_summary to ticket_types on ticket_type_id.
- Use a window SUM over all rows to compute the denominator for revenue_share.
Total implied entries as a function of annual-pass usage X
Using ticket_sales_summary and ticket_types, estimate total park entries implied by the last fiscal year's ticket sales under these assumptions:
- Each single_day ticket unit yields 1 entry.
- Each five_day ticket unit yields 5 entries.
- Each annual_pass unit corresponds to one annual-pass holder, and each holder makes an unknown average number of entries X during their pass.
Write a query that computes:
- entries_from_fixed_rules: total entries contributed by single_day and five_day tickets using the fixed multipliers above.
- annual_pass_holders: the number of annual-pass units sold.
From these two numbers, express total implied entries as a function of X: total_entries(X) = entries_from_fixed_rules + annual_pass_holders * X.
Return a single-row result with entries_from_fixed_rules and annual_pass_holders.
Tables
ticket_types(id INT, name VARCHAR(50), price_usd DECIMAL(10,2), duration_days INT)
ticket_sales(order_id INT, user_id INT, ticket_type_id INT, quantity INT, order_date DATE)
ticket_sales_summary(ticket_type_id INT, units_sold INT)
annual_pass_periods(user_id INT, start_date DATE, end_date DATE)
visits(user_id INT, visit_date DATE)
Hints
- Use a CASE expression on ticket_types.name to apply the correct entries-per-unit multiplier.
- Annual passes should contribute to a separate count of holders, not directly to entries in this step.
Average annual-pass visits per holder during active period
Using annual_pass_periods and visits, compute the empirical average number of visits per annual-pass holder during their active pass period.
Rules:
- Treat each row in annual_pass_periods as one pass held by a user.
- For each pass, count the number of visits where visits.user_id = annual_pass_periods.user_id and visit_date is between start_date and end_date (inclusive).
- If a user has multiple passes over time, treat each pass separately with its own period and visit count.
- Include passes with zero qualifying visits in the average (i.e., use a LEFT JOIN).
- Exclude visits that fall outside the pass's [start_date, end_date] interval.
Return a single row with:
- avg_visits_per_pass: the average number of visits per pass period across all passes.
Tables
ticket_types(id INT, name VARCHAR(50), price_usd DECIMAL(10,2), duration_days INT)
ticket_sales(order_id INT, user_id INT, ticket_type_id INT, quantity INT, order_date DATE)
ticket_sales_summary(ticket_type_id INT, units_sold INT)
annual_pass_periods(user_id INT, start_date DATE, end_date DATE)
visits(user_id INT, visit_date DATE)
Hints
- Start by computing visit counts per pass period with a LEFT JOIN from annual_pass_periods to visits.
- Then take the AVG of those per-pass counts; remember to cast to a non-integer type to get a fractional result.
Total implied entries using empirical annual-pass usage
Using your results from parts (b) and (c), compute the final estimate of total park entries implied by the last fiscal year's ticket sales.
Definition recap:
- From part (b), you have:
- entries_from_fixed_rules: total entries from single_day and five_day tickets.
- annual_pass_holders: number of annual-pass units sold.
- total_entries(X) = entries_from_fixed_rules + annual_pass_holders * X.
- From part (c), you have:
- avg_visits_per_pass: empirical average visits per annual-pass holder during their active period.
In this question, set X = avg_visits_per_pass and compute:
- total_entries = entries_from_fixed_rules + annual_pass_holders * avg_visits_per_pass.
Write a single query (it may use CTEs) that reads from ticket_sales_summary, ticket_types, annual_pass_periods, and visits, and returns one row with total_entries. Using the sample data, this should numerically plug in avg_visits_per_pass from part (c) and the coefficients from part (b).
Tables
ticket_types(id INT, name VARCHAR(50), price_usd DECIMAL(10,2), duration_days INT)
ticket_sales(order_id INT, user_id INT, ticket_type_id INT, quantity INT, order_date DATE)
ticket_sales_summary(ticket_type_id INT, units_sold INT)
annual_pass_periods(user_id INT, start_date DATE, end_date DATE)
visits(user_id INT, visit_date DATE)
Hints
- Reuse the CASE logic from part (b) to get fixed_entries and annual_pass_holders in a CTE.
- Reuse the per-pass averaging logic from part (c) in another CTE, then multiply and sum in the final SELECT.