Coding questions · Meta

Calculate Average Session Duration and Performance Metrics

This question evaluates proficiency in data manipulation and analytics, focusing on session-level time calculations, user behavior metrics such as bounce rate, performance metric definition, and revenue attribution using SQL and Python.

user_sessions

+---------+------------+------+---------------------+---------------------+
| user_id | session_id | app  | start_time          | end_time            |
+---------+------------+------+---------------------+---------------------+
| 123     | 1          | ins  | 2023-07-04 10:00:00 | 2023-07-04 10:15:42 |
| 123     | 2          | fb   | 2023-07-04 10:20:00 | 2023-07-04 10:35:10 |
| 456     | 3          | ins  | 2023-07-04 11:00:00 | 2023-07-04 11:02:07 |
| 789     | 4          | fb   | 2023-07-04 12:00:00 | 2023-07-04 12:45:00 |
| 123     | 5          | ins  | 2023-07-05 09:00:00 | 2023-07-05 09:25:30 |
+---------+------------+------+---------------------+---------------------+

ads_stats

+--------------+------------------+---------+---------+------------+
| advertiser_id | creation_source | country | spend   | date       |
+--------------+------------------+---------+---------+------------+
| 101           | web              | US      | 1500.00 | 2023-06-15 |
| 102           | api              | CA      | 200.00  | 2023-06-15 |
| 103           | mobile           | IN      | 5000.00 | 2023-07-01 |
| 104           | web              | UK      | 750.00  | 2022-07-01 |
| 105           | api              | US      | 1200.00 | 2023-07-02 |
+--------------+------------------+---------+---------+------------+

Scenario

SQL data-manipulation round covering user session analytics and advertiser revenue questions.

Question

Using yesterday’s data, compute the average session duration (end_time ‑ start_time) grouped by app.

Define a performance metric for each app, calculate it, and decide which app performs best.

For every day and app, calculate the bounce rate where a user switches to another app then returns to the first.

Ads tasks:

i) For each creation_source, report daily revenue for the past month.

ii) Find the top 10 least-active advertisers and list their countries.

iii) For every creation_source, compare this-year vs. last-year ratio of advertisers spending > 1000.

iv) Show how to prove a revenue increase from one source is due to decreases in others.

Hints

Use date filtering, TIMESTAMPDIFF, window functions, self-joins for bounces, CTEs, and careful denominator selection for ratios.

Average session duration

Average session duration by app

The user_sessions table records one row per app session, with the session's start and end timestamps.

column	type	notes
`user_id`	INTEGER	the user who owned the session
`session_id`	INTEGER	primary key
`app`	VARCHAR(10)	which app the session was in (e.g. `ins`, `fb`)
`start_time`	TIMESTAMP	when the session began
`end_time`	TIMESTAMP	when the session ended

The analysis is run on 2025-06-01, so "yesterday" is 2025-05-31. Consider only sessions that started on 2025-05-31 (compare on the date portion of start_time).

For each app, compute:

sessions — the number of qualifying sessions.
avg_session_seconds — the average session duration in seconds (where a session's duration is end_time - start_time), rounded to 2 decimals.
avg_session_minutes — the same average expressed in minutes, rounded to 2 decimals.

Return one row per app with columns app, sessions, avg_session_seconds, avg_session_minutes, sorted by app ascending.

Table `user_sessions`

column	type	constraints
user_id	INTEGER	NOT NULL
session_id	INTEGER	PRIMARY KEY, NOT NULL
app	VARCHAR(10)	NOT NULL
start_time	DATETIME	NOT NULL
end_time	DATETIME	NOT NULL

Sample data:

app	end_time	session_id	start_time	user_id
ins	2025-05-31 10:15:42	1	2025-05-31 10:00:00	123
fb	2025-05-31 10:35:10	2	2025-05-31 10:20:00	123
ins	2025-05-31 11:02:07	3	2025-05-31 11:00:00	456
fb	2025-05-31 12:45:00	4	2025-05-31 12:00:00	789
ins	2025-06-01 09:25:30	5	2025-06-01 09:00:00	123

Expected output

One row per app for sessions that started on 2025-05-31, with session count and average duration in seconds and minutes (each rounded to 2 decimals), ordered by app.

app	avg_session_minutes	avg_session_seconds	sessions
fb	30.08	1805.00	2
ins	8.91	534.50	2

Hints

Filter rows where the date part of start_time equals 2025-05-31 (cast with start_time::date).
Postgres has no TIMESTAMPDIFF: get seconds with EXTRACT(EPOCH FROM (end_time - start_time)).
Group by app and use COUNT/AVG; cast to numeric before ROUND(..., 2), and divide seconds by 60 for minutes.

App performance metric

You are given a single table user_sessions that records one row per app session a user opened, with the app used and the session's start and end timestamps.

For sessions that started on 2025-05-31 (the day before 2025-06-01), compute a per-app performance score defined as:

performance_score = avg_session_seconds * (1 - bounce_rate)

where:

avg_session_seconds is the average session duration in seconds for that app on 2025-05-31, i.e. AVG(end_time - start_time) expressed in seconds.
bounce_rate captures the A -> B -> A switch-back pattern. Looking at each user's sessions ordered by start_time, an opportunity is any session that has two subsequent sessions by the same user on the same calendar day (so the session plus its next two all fall on the same day). An opportunity is a bounce when the user switched apps and then switched back, i.e. the current app A differs from the next app B, and the session two steps later is back on the original app A. The bounce is attributed to the first app A of the pattern. For each app, bounce_rate = bounces / opportunities. If an app has zero opportunities on 2025-05-31, treat its bounce_rate as 0.

Required output

Return one row per app that had at least one session starting on 2025-05-31, with columns:

column	meaning
`app`	the app identifier
`avg_session_seconds`	average session duration in seconds (rounded to 2 decimals)
`bounce_rate`	the bounce rate as defined above (rounded to 4 decimals), 0 when no opportunities
`performance_score`	`avg_session_seconds * (1 - bounce_rate)` (rounded to 2 decimals)
`rank`	dense rank of the app by `performance_score`, highest score = rank 1

Sort the result by performance_score descending, breaking ties by app ascending.

Table `user_sessions`

column	type	constraints
user_id	INTEGER	NOT NULL
session_id	INTEGER	PRIMARY KEY, NOT NULL
app	VARCHAR(10)	NOT NULL
start_time	TIMESTAMP	NOT NULL
end_time	TIMESTAMP	NOT NULL

Sample data:

app	end_time	session_id	start_time	user_id
ins	2025-05-31 10:15:42	1	2025-05-31 10:00:00	123
fb	2025-05-31 10:35:10	2	2025-05-31 10:20:00	123
ins	2025-05-31 10:55:00	6	2025-05-31 10:40:00	123
ins	2025-05-31 11:02:07	3	2025-05-31 11:00:00	456
fb	2025-05-31 12:45:00	4	2025-05-31 12:00:00	789
ins	2025-06-01 09:25:30	5	2025-06-01 09:00:00	123

Expected output

One row per app (sessions starting 2025-05-31), with avg duration, bounce_rate, performance_score, and dense rank; sorted by performance_score desc then app asc.

app	avg_session_seconds	bounce_rate	performance_score	rank
fb	1805.00	0.0000	1805.00	1
ins	656.33	1.0000	0.00	2

Hints

Order each user's sessions by start_time and use LEAD(col, 1) and LEAD(col, 2) to peek at the next two sessions.
An opportunity requires all three sessions on the same calendar day; a bounce additionally needs app <> next_app and app = next2_app.
Cast the numerator to ::numeric before dividing to avoid integer division, and COALESCE a missing bounce_rate to 0 before the LEFT JOIN math.

Daily bounce rate by app

Compute the daily bounce rate by app where a bounce is defined as a user visiting app A, then a different app B, then returning to A on the same day. For each day and app, return bounces, opportunities, and bounce_rate (NULL if no opportunities).

Table `user_sessions`

column	type	constraints
user_id	INTEGER	NOT NULL
session_id	INTEGER	PRIMARY KEY, NOT NULL
app	VARCHAR(10)	NOT NULL
start_time	DATETIME	NOT NULL
end_time	DATETIME	NOT NULL

Sample data:

app	end_time	session_id	start_time	user_id
ins	2025-05-31 10:15:42	1	2025-05-31 10:00:00	123
fb	2025-05-31 10:35:10	2	2025-05-31 10:20:00	123
ins	2025-05-31 11:02:07	3	2025-05-31 11:00:00	456
fb	2025-05-31 12:45:00	4	2025-05-31 12:00:00	789
ins	2025-06-01 09:25:30	5	2025-06-01 09:00:00	123

Expected output

Daily bounce metrics by app across all days in the data (2025-05-31 and 2025-06-01). With this sample there are no A->B->A patterns, so opportunities and bounces are 0.

app	bounce_rate	day
fb	NULL	2025-05-31
ins	NULL	2025-05-31
ins	NULL	2025-06-01

Hints

Use LEAD to inspect the next two sessions per user ordered by start_time
Mark bounces and opportunities at the row level and then aggregate by day and app

Daily ads revenue

Assume today's date is 2025-06-01. For the previous calendar month (2025-05-01 to 2025-05-31), compute daily revenue by creation_source from ads_stats. Return date, creation_source, and total revenue for that day/source.

Table `ads_stats`

column	type	constraints
advertiser_id	INTEGER	NOT NULL
creation_source	VARCHAR(20)	NOT NULL
country	CHAR(2)	NOT NULL
spend	DECIMAL(12,2)	NOT NULL
date	DATE	NOT NULL

Sample data:

advertiser_id	country	creation_source	date	spend
101	US	web	2025-05-15	1500
102	CA	api	2025-05-15	200
103	IN	mobile	2025-06-15	5000
104	UK	web	2024-07-01	750
105	US	api	2025-06-20	1200

Expected output

Daily revenue by creation_source for the previous calendar month 2025-05-01 to 2025-05-31. In this sample, only 2025-05-15 has spend.

creation_source	date	revenue
api	2025-05-15	200
web	2025-05-15	1500

Hints

Filter by date BETWEEN '2025-05-01' AND '2025-05-31'
Group by date and creation_source and sum spend as revenue

Least-active advertisers

Find the top 10 least-active advertisers by total spend. Return advertiser_id, country, total_spend, and number of active_days (distinct dates). Order by total_spend ascending, then active_days ascending, then advertiser_id.

Table `ads_stats`

column	type	constraints
advertiser_id	INTEGER	NOT NULL
creation_source	VARCHAR(20)	NOT NULL
country	CHAR(2)	NOT NULL
spend	DECIMAL(12,2)	NOT NULL
date	DATE	NOT NULL

Sample data:

advertiser_id	country	creation_source	date	spend
101	US	web	2025-05-15	1500
102	CA	api	2025-05-15	200
103	IN	mobile	2025-06-15	5000
104	UK	web	2024-07-01	750
105	US	api	2025-06-20	1200

Expected output

Top 10 advertisers with the lowest total spend, including their country and number of active days.

active_days	advertiser_id	country	total_spend
1	102	CA	200
1	104	UK	750
1	105	US	1200
1	101	US	1500
1	103	IN	5000

Hints

Aggregate per advertiser_id to get total spend
Use COUNT(DISTINCT date) to count active days
Order ascending by total_spend, then active_days, then advertiser_id, and LIMIT 10

Big-Spender Ratio Year over Year

Assume the current year is 2025. For each creation_source, compare the share of advertisers with any spend above 1000 in 2025 versus 2024.

Use ads_stats. Count each advertiser at most once per creation_source and year. For each source, compute:

this_year_ratio: 2025 advertisers with any spend greater than 1000 divided by total 2025 advertisers for that source
last_year_ratio: 2024 advertisers with any spend greater than 1000 divided by total 2024 advertisers for that source
yoy_ratio: this_year_ratio / last_year_ratio, only when last_year_ratio > 0; otherwise NULL
ratio_delta: this_year_ratio - last_year_ratio when both ratios exist; otherwise NULL

Order by creation_source.

Table `ads_stats`

column	type	constraints
advertiser_id	INTEGER	NOT NULL
creation_source	VARCHAR(20)	NOT NULL
country	CHAR(2)	NOT NULL
spend	DECIMAL(12,2)	NOT NULL
date	DATE	NOT NULL

Sample data:

advertiser_id	country	creation_source	date	spend
101	US	web	2025-05-15	1500
102	CA	api	2025-05-15	200
103	IN	mobile	2025-06-15	5000
104	UK	web	2024-07-01	750
105	US	api	2025-06-20	1200

Expected output

Ratios for 2025 versus 2024 by creation_source, with guarded year-over-year ratio and delta calculations.

creation_source	last_year_ratio	ratio_delta	this_year_ratio	yoy_ratio
api	NULL	NULL	0.5	NULL
mobile	NULL	NULL	1	NULL
web	0	1	1	NULL

Hints

Use EXTRACT(YEAR FROM date) in PostgreSQL.
Aggregate to advertiser-year before counting ratios so an advertiser is counted once.
Cast the numerator to numeric before division.

Revenue decomposition

Revenue Decomposition: Focus Source vs. Others (Month-over-Month, 2025)

Assume the current year is 2025. We want to investigate whether month-over-month revenue growth for a focus source (creation_source = 'mobile') tends to coincide with revenue declines in all other sources combined.

Input table

ads_stats — one row per advertiser/source/country observation:

column	type	notes
`advertiser_id`	INTEGER	advertiser identifier
`creation_source`	VARCHAR(20)	acquisition channel, e.g. `'mobile'`, `'web'`, `'api'`
`country`	CHAR(2)	ISO country code
`spend`	DECIMAL(12,2)	revenue (treat `spend` as the revenue measure)
`date`	DATE	observation date

Task

Keep only rows in calendar year 2025.
Aggregate spend to the calendar month (first day of the month) and split it into two buckets per month: focus (creation_source = 'mobile') and others (every other source combined).
For each pair of consecutive months that both appear in the data, compute the change from the previous month to the current month for the focus bucket and for the others bucket.
Output one row per consecutive-month transition (skip the earliest month, which has no prior month).

Required output columns

prev_period — first day (DATE) of the earlier month
curr_period — first day (DATE) of the later month
focus_change — current focus revenue minus previous focus revenue
others_change — current others revenue minus previous others revenue
net_change — focus_change + others_change
increase_due_to_decrease_flag — 1 when focus_change > 0 and others_change < 0, otherwise 0

Sort the result by curr_period ascending.

Table `ads_stats`

column	type	constraints
advertiser_id	INTEGER	NOT NULL
creation_source	VARCHAR(20)	NOT NULL
country	CHAR(2)	NOT NULL
spend	DECIMAL(12,2)	NOT NULL
date	DATE	NOT NULL

Sample data:

advertiser_id	country	creation_source	date	spend
101	US	web	2025-05-15	1500
102	CA	api	2025-05-15	200
103	IN	mobile	2025-06-15	5000
104	UK	web	2024-07-01	750
105	US	api	2025-06-20	1200

Expected output

One row per consecutive-month transition in 2025: the focus ('mobile') and others changes between the two months, the net change, and a flag set to 1 when focus rose while others fell.

curr_period	focus_change	increase_due_to_decrease_flag	net_change	others_change	prev_period
2025-06-01	5000.00	1	4500.00	-500.00	2025-05-01

Hints

Roll dates up to the first of the month with DATE_TRUNC('month', date)::date, and filter the year with EXTRACT(YEAR FROM date) = 2025.
Use conditional SUMs (CASE WHEN creation_source = 'mobile' ...) to split each month into a focus bucket and an others bucket.
Bring the previous month's figures onto each row with LAG(...) OVER (ORDER BY month_start), then drop rows where the previous month is NULL.

Scenario

Question

Hints

Average session duration

Average session duration by app

Table user_sessions

Expected output

Hints

App performance metric

Required output

Table user_sessions

Expected output

Hints

Daily bounce rate by app

Table user_sessions

Expected output

Hints

Daily ads revenue

Table ads_stats

Expected output

Hints

Least-active advertisers

Table ads_stats

Expected output

Hints

Big-Spender Ratio Year over Year

Table ads_stats

Expected output

Hints

Revenue decomposition

Revenue Decomposition: Focus Source vs. Others (Month-over-Month, 2025)

Input table

Task

Required output columns

Table ads_stats

Expected output

Hints

Table `user_sessions`

Table `user_sessions`

Table `user_sessions`

Table `ads_stats`

Table `ads_stats`

Table `ads_stats`

Table `ads_stats`