Design SQL Query for Shop Visibility and User Activity Metrics

SHOP_VISIBILITY

+----------+---------+------------+------------+-------------+--------------+ | user_id | shop_id | event_date | is_visible | signup_date | action_type | +----------+---------+------------+------------+-------------+--------------+ | 101 | 12 | 2023-06-01 | TRUE | 2023-05-20 | view | | 102 | 18 | 2023-06-01 | FALSE | 2021-11-02 | view | | 103 | 44 | 2023-06-02 | TRUE | 2023-06-01 | click | | 104 | 12 | 2023-06-02 | TRUE | 2020-01-15 | purchase | | 105 | 18 | 2023-06-02 | FALSE | 2023-05-30 | view | +----------+---------+------------+------------+-------------+--------------+

Scenario

E-commerce ‘shop visibility’ dataset used to test SQL skills and metric design.

Question

Write a query that for each shop returns total visible days, ordered descending (GROUP BY, HAVING, ORDER BY). Extend it to include shop category with a JOIN and apply CASE logic for visibility buckets. Using window functions, rank shops by average daily visibility within their category. Define and implement a metric—using today’s snapshot—to compare activity of new versus old users by grouping users into tenure buckets and reporting activity rates.

Hints

Compute tenure as DATEDIFF(today, signup_date); activity = active_events / users.