Write SQL for shop visibility and activity metric

You work with three tables: shops, listings, and shop_events. On the fixed snapshot date 2025-06-01, the listings table records whether each listing is visible via the is_visible column (1 = visible, 0 = hidden). Define a shop's visibility_rate on 2025-06-01 as the average of is_visible across all of that shop's listings (i.e., AVG(is_visible) for that shop). Write a SQL query to: 1) Compute visibility_rate per shop. 2) Return only shops that have at least 5 listings. 3) Output columns: shop_id, visibility_rate, total_listings. 4) Sort the result in descending order of visibility_rate, breaking ties by total_listings in descending order (and optionally by shop_id ascending for deterministic output).

Using the same tables, define metrics relative to the fixed reference date 2025-06-01. Definitions: - A 'new shop' is a shop with created_at >= '2025-05-02' (shops opened in the last 30 days, from 2025-05-02 to 2025-06-01 inclusive). - The visibility_rate for a shop is the average of is_visible across all its listings on the 2025-06-01 snapshot. If a shop has no listings, its visibility_rate should be NULL. - The activity_score for a shop over the window from '2025-05-19' to '2025-06-01' (inclusive) is defined as: activity_score = 3 * order_placed + 1 * product_created + 0.5 * listing_updated + 0.2 * message_replied where order_placed, product_created, listing_updated, and message_replied are the summed cnt values from shop_events of each event_type over that date range. - Shops with no events in that window should have activity_score = 0. Write a SQL query that outputs one row per shop with the columns: - shop_id - is_new (1 for new shops, 0 for existing shops) - visibility_rate (NULL if the shop has no listings) - activity_score (0 if the shop has no events in the window) Order the result by shop_id ascending.

You are analyzing seller shops on a marketplace. You have three tables: **`shops`** — one row per shop. | column | type | notes | |---|---|---| | `shop_id` | INT | primary key | | `created_at` | DATE | date the shop was created | **`listings`** — one row per product listing (provided for context; not required for this question). | column | type | notes | |---|---|---| | `listing_id` | INT | primary key | | `shop_id` | INT | the shop the listing belongs to | | `created_at` | DATE | date the listing was created | | `is_visible` | SMALLINT | 1 if the listing is publicly visible, else 0 | **`shop_events`** — one row per (shop, day, event type), with an aggregated count. | column | type | notes | |---|---|---| | `shop_id` | INT | the shop | | `event_date` | DATE | day the events occurred | | `event_type` | VARCHAR(32) | one of `order_placed`, `product_created`, `listing_updated`, `message_replied` | | `cnt` | INT | number of events of that type on that day | **Per-shop definitions (carried over from the previous question):** - A shop is **new** (`is_new = 1`) if `created_at >= '2025-05-02'`, otherwise it is **existing** (`is_new = 0`). - A shop's **`activity_score`** is computed over the window `event_date` from `'2025-05-19'` to `'2025-06-01'` (inclusive on both ends) as a weighted sum of its events: - `order_placed` → 3.0 per event - `product_created` → 1.0 per event - `listing_updated` → 0.5 per event - `message_replied` → 0.2 per event A shop with no events in that window has `activity_score = 0`. (Every shop in `shops` must appear, scored 0 if it has no qualifying events.) **Task:** Produce a summary that compares **new** vs **existing** shops across three metrics. Return **exactly three rows** with these four columns: - `metric_name` — the metric label (text). - `new_shops_value` — the metric's value for new shops. - `existing_shops_value` — the metric's value for existing shops. - `pct_diff` — defined as `100 * (new_shops_value - existing_shops_value) / existing_shops_value`; return `NULL` if `existing_shops_value` is 0. The three metrics (one row each) are: 1. `mean_activity_score` — the average `activity_score` across all shops in the group. 2. `median_activity_score` — the median `activity_score` across all shops in the group, computed with an analytic/ordered-set function (`PERCENTILE_CONT(0.5)`). 3. `share_of_active_shops` — the fraction of shops in the group whose `activity_score > 0`. Round every numeric value (`new_shops_value`, `existing_shops_value`, `pct_diff`) to **4 decimal places**. Order the result rows alphabetically by `metric_name`.

Using the per-shop metrics from Question 2 (shop_id, is_new, visibility_rate, activity_score), find new shops that are active but have low visibility. Definitions recap: - is_new = 1 if created_at >= '2025-05-02', else 0. - visibility_rate is the average is_visible across the shop's listings on 2025-06-01; shops with no listings have visibility_rate = NULL. - activity_score is computed over 2025-05-19 to 2025-06-01 as in Question 2. Write a SQL query to: 1) Consider only new shops (is_new = 1). 2) Among them, filter to shops with visibility_rate < 0.5. (Assume that shops with visibility_rate = NULL are excluded from this filter.) 3) Return the top 5 such shops ordered by activity_score in descending order, breaking ties by shop_id ascending. 4) Output columns: shop_id, visibility_rate, activity_score. In an interview setting, you should also be ready to explain: - Why visibility_rate is NULL (not 0) for shops with no listings, and why activity_score is 0 (not NULL) for shops with no events. - How you would prevent survivorship bias in a real analysis (e.g., by ensuring the denominator includes shops that churned before the snapshot date), but you do not need to express that explanation in SQL here.