Company: Snowflake | Difficulty: hard
Snowflake's product analytics team stores each user's clicked products as a JSON array inside a session log. Every item in the clicked_items array represents one click, so the same product may appear multiple times in one session.
clicked_items
Find the most frequently clicked catalog product during the first quarter of 2025.
A repeated product in one session counts as multiple clicks but only one unique user and one session value. If a session contains multiple products, its session_value must be counted once for each distinct product in that session. Treat a missing session_value as zero, and ignore any product_id that is not present in product_catalog.
session_value
product_id
product_catalog
If products tie on click_count, prefer the product with more unique_users. If they still tie, prefer the larger product_id. Return only the single winning product.
click_count
unique_users
Return the columns in this exact order: product_id, product_name, click_count, unique_users, and total_session_value.
product_name
total_session_value
session_logs
session_id
user_id
logged_at
Aurora Editor receives four clicks: two from session 1, one from session 3, and one from session 5. It is clicked by two unique users. Its session value is 180.00 because sessions 1, 3, and 5 are counted once each, with the missing value in session 5 treated as zero. No other catalog product has as many clicks.
DROP TABLE IF EXISTS session_logs; DROP TABLE IF EXISTS product_catalog; CREATE TABLE product_catalog ( product_id INT, product_name VARCHAR(100) ); CREATE TABLE session_logs ( session_id INT, user_id INT, logged_at TIMESTAMP, clicked_items JSONB, session_value DECIMAL(10,2) ); INSERT INTO product_catalog VALUES (101, 'Aurora Editor'), (102, 'Nimbus Storage'), (103, 'Orbit Analytics'); INSERT INTO session_logs VALUES (1, 101, '2025-01-05 09:00:00', '[101, 102, 101]', 100.00), (2, 102, '2025-01-12 10:00:00', '[102, 103]', 50.00), (3, 101, '2025-02-02 11:00:00', '[101, 103, 103]', 80.00), (4, 103, '2025-02-15 12:00:00', '[]', 20.00), (5, 103, '2025-03-03 13:00:00', '[101]', NULL), (6, 104, '2024-12-31 14:00:00', '[102, 103]', 200.00);
← All SQL Challenges · Snowflake Questions · Learn SQL Free