Coding questions · Capital One

Write SQL for theme-park revenue and visits

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.

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.

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.

Table `ticket_types`

column	type	constraints
id	INT	PRIMARY KEY
name	VARCHAR(50)	NOT NULL
price_usd	DECIMAL(10,2)	NOT NULL
duration_days	INT	NOT NULL

Sample data:

duration_days	id	name	price_usd
1	1	single_day	80
5	2	five_day	300
365	3	annual_pass	1000

Table `ticket_sales`

column	type	constraints
order_id	INT	PRIMARY KEY
user_id	INT	NOT NULL
ticket_type_id	INT	NOT NULL
quantity	INT	NOT NULL
order_date	DATE	NOT NULL

Sample data:

order_date	order_id	quantity	ticket_type_id	user_id
2024-03-15	101	1	3	1
2024-06-01	102	2	1	2
2024-07-10	103	1	2	3
2024-08-20	104	1	1	4
2024-08-21	105	1	3	5

Table `ticket_sales_summary`

column	type	constraints
ticket_type_id	INT	PRIMARY KEY
units_sold	INT	NOT NULL

Sample data:

ticket_type_id	units_sold
1	250000
2	100000
3	10000

Table `annual_pass_periods`

column	type	constraints
user_id	INT	NOT NULL
start_date	DATE	NOT NULL
end_date	DATE	NOT NULL

Sample data:

end_date	start_date	user_id
2025-03-14	2024-03-15	1
2025-08-20	2024-08-21	5

Table `visits`

column	type	constraints
user_id	INT	NOT NULL
visit_date	DATE	NOT NULL

Sample data:

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

Expected output

One row per ticket type, showing revenue and its share of total revenue. Based on the sample data, total revenue is $60,000,000, with single_day contributing $20,000,000, five_day $30,000,000, and annual_pass $10,000,000.

name	revenue_share	revenue_usd	ticket_type_id
single_day	0.333333	20000000	1
five_day	0.5	30000000	2
annual_pass	0.166667	10000000	3

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.

Table `ticket_types`

column	type	constraints
id	INT	PRIMARY KEY
name	VARCHAR(50)	NOT NULL
price_usd	DECIMAL(10,2)	NOT NULL
duration_days	INT	NOT NULL

Sample data:

duration_days	id	name	price_usd
1	1	single_day	80
5	2	five_day	300
365	3	annual_pass	1000

Table `ticket_sales`

column	type	constraints
order_id	INT	PRIMARY KEY
user_id	INT	NOT NULL
ticket_type_id	INT	NOT NULL
quantity	INT	NOT NULL
order_date	DATE	NOT NULL

Sample data:

order_date	order_id	quantity	ticket_type_id	user_id
2024-03-15	101	1	3	1
2024-06-01	102	2	1	2
2024-07-10	103	1	2	3
2024-08-20	104	1	1	4
2024-08-21	105	1	3	5

Table `ticket_sales_summary`

column	type	constraints
ticket_type_id	INT	PRIMARY KEY
units_sold	INT	NOT NULL

Sample data:

ticket_type_id	units_sold
1	250000
2	100000
3	10000

Table `annual_pass_periods`

column	type	constraints
user_id	INT	NOT NULL
start_date	DATE	NOT NULL
end_date	DATE	NOT NULL

Sample data:

end_date	start_date	user_id
2025-03-14	2024-03-15	1
2025-08-20	2024-08-21	5

Table `visits`

column	type	constraints
user_id	INT	NOT NULL
visit_date	DATE	NOT NULL

Sample data:

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

Expected output

A single row with the total entries implied by single_day and five_day tickets, and the count of annual-pass holders. For the sample data: entries_from_fixed_rules = 750,000 (250,000 single_day * 1 + 100,000 five_day * 5) and annual_pass_holders = 10,000. Thus total_entries(X) = 750000 + 10000 * X.

annual_pass_holders	entries_from_fixed_rules
10000	750000

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.

Table `ticket_types`

column	type	constraints
id	INT	PRIMARY KEY
name	VARCHAR(50)	NOT NULL
price_usd	DECIMAL(10,2)	NOT NULL
duration_days	INT	NOT NULL

Sample data:

duration_days	id	name	price_usd
1	1	single_day	80
5	2	five_day	300
365	3	annual_pass	1000

Table `ticket_sales`

column	type	constraints
order_id	INT	PRIMARY KEY
user_id	INT	NOT NULL
ticket_type_id	INT	NOT NULL
quantity	INT	NOT NULL
order_date	DATE	NOT NULL

Sample data:

order_date	order_id	quantity	ticket_type_id	user_id
2024-03-15	101	1	3	1
2024-06-01	102	2	1	2
2024-07-10	103	1	2	3
2024-08-20	104	1	1	4
2024-08-21	105	1	3	5

Table `ticket_sales_summary`

column	type	constraints
ticket_type_id	INT	PRIMARY KEY
units_sold	INT	NOT NULL

Sample data:

ticket_type_id	units_sold
1	250000
2	100000
3	10000

Table `annual_pass_periods`

column	type	constraints
user_id	INT	NOT NULL
start_date	DATE	NOT NULL
end_date	DATE	NOT NULL

Sample data:

end_date	start_date	user_id
2025-03-14	2024-03-15	1
2025-08-20	2024-08-21	5

Table `visits`

column	type	constraints
user_id	INT	NOT NULL
visit_date	DATE	NOT NULL

Sample data:

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

Expected output

A single row with the average visits per annual-pass period. For the sample data, user 1 has 3 visits during 2024-03-15 to 2025-03-14, user 5 has 2 visits during 2024-08-21 to 2025-08-20, so avg_visits_per_pass = (3 + 2) / 2 = 2.5.

avg_visits_per_pass
2.5

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).

Table `ticket_types`

column	type	constraints
id	INT	PRIMARY KEY
name	VARCHAR(50)	NOT NULL
price_usd	DECIMAL(10,2)	NOT NULL
duration_days	INT	NOT NULL

Sample data:

duration_days	id	name	price_usd
1	1	single_day	80
5	2	five_day	300
365	3	annual_pass	1000

Table `ticket_sales`

column	type	constraints
order_id	INT	PRIMARY KEY
user_id	INT	NOT NULL
ticket_type_id	INT	NOT NULL
quantity	INT	NOT NULL
order_date	DATE	NOT NULL

Sample data:

order_date	order_id	quantity	ticket_type_id	user_id
2024-03-15	101	1	3	1
2024-06-01	102	2	1	2
2024-07-10	103	1	2	3
2024-08-20	104	1	1	4
2024-08-21	105	1	3	5

Table `ticket_sales_summary`

column	type	constraints
ticket_type_id	INT	PRIMARY KEY
units_sold	INT	NOT NULL

Sample data:

ticket_type_id	units_sold
1	250000
2	100000
3	10000

Table `annual_pass_periods`

column	type	constraints
user_id	INT	NOT NULL
start_date	DATE	NOT NULL
end_date	DATE	NOT NULL

Sample data:

end_date	start_date	user_id
2025-03-14	2024-03-15	1
2025-08-20	2024-08-21	5

Table `visits`

column	type	constraints
user_id	INT	NOT NULL
visit_date	DATE	NOT NULL

Sample data:

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

Expected output

A single row with the total implied entries using empirical annual-pass usage. From part (b), entries_from_fixed_rules = 750,000 and annual_pass_holders = 10,000. From part (c), avg_visits_per_pass = 2.5. Thus total_entries = 750,000 + 10,000 * 2.5 = 775,000.

total_entries
775000

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.

Theme-park revenue and revenue share by ticket type

Table ticket_types

Table ticket_sales

Table ticket_sales_summary

Table annual_pass_periods

Table visits

Expected output

Hints

Total implied entries as a function of annual-pass usage X

Table ticket_types

Table ticket_sales

Table ticket_sales_summary

Table annual_pass_periods

Table visits

Expected output

Hints

Average annual-pass visits per holder during active period

Table ticket_types

Table ticket_sales

Table ticket_sales_summary

Table annual_pass_periods

Table visits

Expected output

Hints

Total implied entries using empirical annual-pass usage

Table ticket_types

Table ticket_sales

Table ticket_sales_summary

Table annual_pass_periods

Table visits

Expected output

Hints

Table `ticket_types`

Table `ticket_sales`

Table `ticket_sales_summary`

Table `annual_pass_periods`

Table `visits`

Table `ticket_types`

Table `ticket_sales`

Table `ticket_sales_summary`

Table `annual_pass_periods`

Table `visits`

Table `ticket_types`

Table `ticket_sales`

Table `ticket_sales_summary`

Table `annual_pass_periods`

Table `visits`

Table `ticket_types`

Table `ticket_sales`

Table `ticket_sales_summary`

Table `annual_pass_periods`

Table `visits`