cswork / libraryOpen practice workspace

Coding questions · Meta

Write SQL to analyze shop visibility

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.

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.

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.

Table shop_visibility

columntypeconstraints
profile_idBIGINTNOT NULL
dateDATENOT NULL
tsTIMESTAMPNOT NULL
visibilityINTNOT NULL

Sample data:

dateprofile_idtsvisibility
2025-08-301012025-08-30 09:00:001
2025-08-311012025-08-31 11:00:000
2025-09-011012025-09-01 10:15:001
2025-08-301022025-08-30 08:00:001
2025-08-301022025-08-30 12:00:001
2025-09-011022025-09-01 18:00:000
2025-08-311032025-08-31 09:00:000
2025-09-011032025-09-01 20:00:000
2025-08-311042025-08-31 05:00:000
2025-09-011042025-09-01 09:00:001
2025-09-011042025-09-01 12:30:000
2025-09-011042025-09-01 16:45:001
2025-09-011052025-09-01 07:00:001
2025-08-301062025-08-30 06:00:000
2025-08-301062025-08-30 14:00:001
2025-08-311062025-08-31 14:05:000
2025-08-311062025-08-31 14:10:001
2025-09-011062025-09-01 02:00:000
2025-09-011062025-09-01 22:00:001

Table shop_details

columntypeconstraints
profile_idBIGINTNOT NULL
shop_categoryVARCHAR(50)NOT NULL
visible_daysBIGINT
creation_dateDATENOT NULL

Sample data:

creation_dateprofile_idshop_categoryvisible_days
2025-02-01101Food180
2025-03-15102Fashion120
2025-08-10103Electronics30
2025-07-20104Food75
2024-12-05105Home200
2025-08-28106Fashion10

Expected output

Top profiles by number of visibility flips, ordered by flip_count descending and profile_id ascending. Profiles with at least one visibility flip are returned.

flip_countprofile_id
5106
3104
2101
1102

Hints

  1. Use LAG(visibility) OVER (PARTITION BY profile_id ORDER BY ts) to compare each row to the previous state.
  2. 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.

Table shop_visibility

columntypeconstraints
profile_idBIGINTNOT NULL
dateDATENOT NULL
tsTIMESTAMPNOT NULL
visibilityINTNOT NULL

Sample data:

dateprofile_idtsvisibility
2025-08-301012025-08-30 09:00:001
2025-08-311012025-08-31 11:00:000
2025-09-011012025-09-01 10:15:001
2025-08-301022025-08-30 08:00:001
2025-08-301022025-08-30 12:00:001
2025-09-011022025-09-01 18:00:000
2025-08-311032025-08-31 09:00:000
2025-09-011032025-09-01 20:00:000
2025-08-311042025-08-31 05:00:000
2025-09-011042025-09-01 09:00:001
2025-09-011042025-09-01 12:30:000
2025-09-011042025-09-01 16:45:001
2025-09-011052025-09-01 07:00:001
2025-08-301062025-08-30 06:00:000
2025-08-301062025-08-30 14:00:001
2025-08-311062025-08-31 14:05:000
2025-08-311062025-08-31 14:10:001
2025-09-011062025-09-01 02:00:000
2025-09-011062025-09-01 22:00:001

Table shop_details

columntypeconstraints
profile_idBIGINTNOT NULL
shop_categoryVARCHAR(50)NOT NULL
visible_daysBIGINT
creation_dateDATENOT NULL

Sample data:

creation_dateprofile_idshop_categoryvisible_days
2025-02-01101Food180
2025-03-15102Fashion120
2025-08-10103Electronics30
2025-07-20104Food75
2024-12-05105Home200
2025-08-28106Fashion10

Expected output

A single row giving the count of currently-visible profiles, the total number of profiles in shop_details, and the visible percentage rounded to two decimals.

total_profilesvisible_percentagevisible_profiles
666.674

Hints

  1. 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.
  2. 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.
  3. Multiply by 100.0 (not 100) before ROUND(..., 2) so the division is numeric rather than integer.

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.

Table shop_details

columntypeconstraints
profile_idINTEGER
shop_categoryVARCHAR(50)

Sample data:

profile_idshop_category
1Food
2Food
3Fashion
4Home
5Food
6Fashion
7Home

Table shop_visibility

columntypeconstraints
profile_idINTEGER
visibilityINTEGER
tsTIMESTAMP

Sample data:

profile_idtsvisibility
12025-08-01 09:00:000
12025-08-20 14:30:001
22025-08-10 08:15:001
32025-08-15 11:45:001
42025-07-01 10:00:000
42025-08-25 16:20:001
52025-08-05 07:30:001
52025-08-28 19:00:000
62025-09-05 12:00:001

Expected output

One row per category that has at least one currently-visible profile (as of 2025-09-01 23:59:59), with its visible profile count and percentage share of all currently-visible profiles, ordered by visible_count desc then shop_category asc.

shop_categoryvisible_countvisible_share_percentage
Food250.00
Fashion125.00
Home125.00

Hints

  1. 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.
  2. 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.
  3. Get each category's share with SUM(COUNT()) OVER () as the grand total, then ROUND(100.0 * COUNT() / total, 2).