Quick Overview

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.

Calculate Average Session Duration and Performance Metrics

Company: Meta

Role: Data Scientist

Category: Data Manipulation (SQL/Python)

Difficulty: medium

Interview Round: Onsite

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.

Overview: 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.

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: 1. `sessions` — the number of qualifying sessions. 2. `avg_session_seconds` — the average session duration in seconds (where a session's duration is `end_time - start_time`), rounded to 2 decimals. 3. `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.

Tables

user_sessions(user_id INTEGER, session_id INTEGER, app VARCHAR(10), start_time DATETIME, end_time DATETIME)

Hints

  1. Filter rows where the date part of start_time equals 2025-05-31 (cast with start_time::date).
  2. Postgres has no TIMESTAMPDIFF: get seconds with EXTRACT(EPOCH FROM (end_time - start_time)).

App performance metric

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

Tables

user_sessions(user_id INTEGER, session_id INTEGER, app VARCHAR(10), start_time TIMESTAMP, end_time TIMESTAMP)

Hints

  1. Order each user's sessions by start_time and use LEAD(col, 1) and LEAD(col, 2) to peek at the next two sessions.
  2. An opportunity requires all three sessions on the same calendar day; a bounce additionally needs app <> next_app and app = next2_app.

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

Tables

user_sessions(user_id INTEGER, session_id INTEGER, app VARCHAR(10), start_time DATETIME, end_time DATETIME)

Hints

  1. Use LEAD to inspect the next two sessions per user ordered by start_time
  2. 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.

Tables

ads_stats(advertiser_id INTEGER, creation_source VARCHAR(20), country CHAR(2), spend DECIMAL(12,2), date DATE)

Hints

  1. Filter by date BETWEEN '2025-05-01' AND '2025-05-31'
  2. 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.

Tables

ads_stats(advertiser_id INTEGER, creation_source VARCHAR(20), country CHAR(2), spend DECIMAL(12,2), date DATE)

Hints

  1. Aggregate per advertiser_id to get total spend
  2. Use COUNT(DISTINCT date) to count active days

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

Tables

ads_stats(advertiser_id INTEGER, creation_source VARCHAR(20), country CHAR(2), spend DECIMAL(12,2), date DATE)

Hints

  1. Use EXTRACT(YEAR FROM date) in PostgreSQL.
  2. Aggregate to advertiser-year before counting ratios so an advertiser is counted once.

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 1. Keep only rows in **calendar year 2025**. 2. 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). 3. 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. 4. 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.

Tables

ads_stats(advertiser_id INTEGER, creation_source VARCHAR(20), country CHAR(2), spend DECIMAL(12,2), date DATE)

Hints

  1. 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.
  2. Use conditional SUMs (CASE WHEN creation_source = 'mobile' ...) to split each month into a focus bucket and an others bucket.

Loading coding console...