Snowflake's product analytics team stores each user's clicked products as a JSON array inside a session log. Every item in the array represents one click, so the same product may appear multiple times in one session. Find the most frequently clicked catalog product during the first quarter of 2025. Return the product ID, product name, total click count, number of unique users who clicked it, and total session value associated with it. 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 missing session values as zero and ignore product IDs that are not present in the 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 winning product. ### Table Schema | Table | Column | Type | |---|---|---| | session_logs | session_id | integer | | session_logs | user_id | integer | | session_logs | logged_at | timestamp | | session_logs | clicked_items | jsonb | | session_logs | session_value | decimal(10,2) | | product_catalog | product_id | integer | | product_catalog | product_name | varchar(100) | ### Example Input: session_logs | session_id | user_id | logged_at | clicked_items | session_value | |---:|---:|---|---|---:| | 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 | ### Example Input: product_catalog | product_id | product_name | |---:|---| | 101 | Aurora Editor | | 102 | Nimbus Storage | | 103 | Orbit Analytics | ### Example Output Explanation 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);