Write SQL to analyze shop visibility
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
You are given two tables. Use standard SQL (window functions allowed). Assume "today" is 2025-09-01 and that “currently visible” means a shop’s last state at or before 2025-09-01 23:59:59. Treat only changes where visibility flips (ignore consecutive duplicates).
Schema:
shop_visibility(profile_id BIGINT, date DATE, ts TIMESTAMP, visibility INT) — 0=hidden, 1=visible
shop_details(profile_id BIGINT, shop_category VARCHAR, visible_days BIGINT, creation_date DATE)
Small sample (illustrative only):
shop_visibility
+------------+------------+---------------------+------------+
| profile_id | date | ts | visibility |
+------------+------------+---------------------+------------+
| 101 | 2025-08-30 | 2025-08-30 09:00:00 | 1 |
| 101 | 2025-08-31 | 2025-08-31 11:00:00 | 0 |
| 101 | 2025-09-01 | 2025-09-01 10:15:00 | 1 |
| 102 | 2025-08-30 | 2025-08-30 08:00:00 | 1 |
| 102 | 2025-08-30 | 2025-08-30 12:00:00 | 1 |
| 102 | 2025-09-01 | 2025-09-01 18:00:00 | 0 |
| 103 | 2025-08-31 | 2025-08-31 09:00:00 | 0 |
| 103 | 2025-09-01 | 2025-09-01 20:00:00 | 0 |
| 104 | 2025-08-31 | 2025-08-31 05:00:00 | 0 |
| 104 | 2025-09-01 | 2025-09-01 09:00:00 | 1 |
| 104 | 2025-09-01 | 2025-09-01 12:30:00 | 0 |
| 104 | 2025-09-01 | 2025-09-01 16:45:00 | 1 |
| 105 | 2025-09-01 | 2025-09-01 07:00:00 | 1 |
| 106 | 2025-08-30 | 2025-08-30 06:00:00 | 0 |
| 106 | 2025-08-30 | 2025-08-30 14:00:00 | 1 |
| 106 | 2025-08-31 | 2025-08-31 14:05:00 | 0 |
| 106 | 2025-08-31 | 2025-08-31 14:10:00 | 1 |
| 106 | 2025-09-01 | 2025-09-01 02:00:00 | 0 |
| 106 | 2025-09-01 | 2025-09-01 22:00:00 | 1 |
shop_details
+------------+---------------+--------------+--------------+
| profile_id | shop_category | visible_days | creation_date|
+------------+---------------+--------------+--------------+
| 101 | Food | 180 | 2025-02-01 |
| 102 | Fashion | 120 | 2025-03-15 |
| 103 | Electronics | 30 | 2025-08-10 |
| 104 | Food | 75 | 2025-07-20 |
| 105 | Home | 200 | 2024-12-05 |
| 106 | Fashion | 10 | 2025-08-28 |
Write SQL for:
1) Top 10 profiles by number of visibility flips (count transitions where visibility != LAG(visibility) over ts). Break ties by smaller profile_id first.
2) Percentage of shops currently visible as of 2025-09-01 (numerator: profiles whose last state by ts on or before 2025-09-01 23:59:59 is 1; denominator: all profiles in shop_details). Return both percentage and counts.
3) Distribution of currently visible shops across categories: for each shop_category, return visible_count and its share among all currently visible shops.
4) Verify: “New users over time are visible far fewer days.” For each creation month, compute for every profile the fraction of days visible in its first 30 days since creation (using shop_visibility, deduping consecutive identical states, counting distinct dates with final visibility=1 per date), then return for each cohort: number of profiles, median fraction, and P75/P25. Include SQL that produces a cohort-level time series suitable for visualization. Briefly state how you would handle profiles created after 2025-08-02 (truncate the 30-day window) and how to test the trend statistically.
Overview: This question evaluates proficiency in SQL-based time-series and event-data manipulation, including deduplication of consecutive states, window functions, aggregation, cohort analysis, and percentile/summary statistics.
Read the full Meta Data Scientist interview experience this question came from
Top profiles by visibility flips
Using the tables below, write a SQL query to find the top 10 profiles by the number of visibility flips. A visibility flip is any row where the visibility value (0=hidden, 1=visible) is different from the previous row for that profile when ordered by ts; ignore consecutive duplicate states. Return profile_id and flip_count, sorted by flip_count in descending order and then by profile_id ascending, limited to at most 10 rows.
Tables
shop_visibility(profile_id BIGINT, date DATE, ts TIMESTAMP, visibility INT)
shop_details(profile_id BIGINT, shop_category VARCHAR(50), visible_days BIGINT, creation_date DATE)
Hints
- Use LAG(visibility) OVER (PARTITION BY profile_id ORDER BY ts) to compare each row to the previous state.
- Count only rows where visibility differs from the previous visibility, then aggregate by profile_id and order by that count.
Percentage of shops currently visible as of 2025-09-01
You are analyzing shop visibility for Meta's commerce platform. Each shop profile logs visibility-state changes over time in `shop_visibility` (`visibility` = 1 means visible, 0 means hidden), and every profile has one descriptive row in `shop_details`.
A shop profile is considered **currently visible as of `2025-09-01 23:59:59`** if its **last** visibility record at or before that timestamp has `visibility = 1`. A profile that has **no** visibility records at or before the cutoff is treated as **not visible**.
Write a SQL query that returns a **single row** with these three columns, in this order:
- `visible_profiles` — the number of profiles that are currently visible as of `2025-09-01 23:59:59`.
- `total_profiles` — the total number of profiles in `shop_details`.
- `visible_percentage` — `visible_profiles / total_profiles * 100`, rounded to two decimal places.
The denominator is the full set of profiles in `shop_details`, including any profile that has no visibility records.
Tables
shop_visibility(profile_id BIGINT, date DATE, ts TIMESTAMP, visibility INT)
shop_details(profile_id BIGINT, shop_category VARCHAR(50), visible_days BIGINT, creation_date DATE)
Hints
- Use ROW_NUMBER() OVER (PARTITION BY profile_id ORDER BY ts DESC) to isolate each profile's most recent visibility record on or before the cutoff timestamp.
- Drive the query from shop_details with a LEFT JOIN so profiles with no visibility logs still count in the denominator; COALESCE the missing visibility to 0.
Category distribution of currently visible shops
A shop profile is considered **currently visible** as of `2025-09-01 23:59:59` if its most recent visibility state with a timestamp **on or before** that moment has `visibility = 1`. A profile with no visibility events on or before the cutoff is treated as **not** currently visible.
You are given two tables:
- **`shop_details`** — one row per shop profile (`profile_id`, `shop_category`).
- **`shop_visibility`** — a log of visibility state changes (`profile_id`, `visibility`, `ts`), where `visibility = 1` means the profile became visible and `visibility = 0` means it became hidden.
Write a SQL query that returns, for **each `shop_category`**, the number of currently visible profiles and that category's percentage share among all currently visible profiles.
The result must contain exactly these columns:
- `shop_category` — the category name.
- `visible_count` — the number of currently visible profiles in that category.
- `visible_share_percentage` — `visible_count` divided by the total number of currently visible profiles across all categories, expressed as a percentage **rounded to two decimal places** (e.g. `50.00`).
Return only categories that have **at least one** currently visible profile. Order the result by `visible_count` **descending**, then by `shop_category` **ascending**.
Tables
shop_details(profile_id INTEGER, shop_category VARCHAR(50))
shop_visibility(profile_id INTEGER, visibility INTEGER, ts TIMESTAMP)
Hints
- For each profile, find its latest visibility event with ts on or before the cutoff using ROW_NUMBER() OVER (PARTITION BY profile_id ORDER BY ts DESC), and keep only rn = 1.
- LEFT JOIN shop_details to that latest state and COALESCE the visibility to 0 so profiles with no qualifying events count as not visible; keep only the ones equal to 1.