Snowflake's platform team wants to identify power users based on their query session behavior, not just raw credit spend, to avoid over-weighting users who simply run one long uninterrupted batch job. A **session** is defined as a sequence of one or more queries by the same user where no two consecutive queries are more than 30 minutes apart. If the gap between a query and the previous query from the same user exceeds 30 minutes, a new session begins (a gap of exactly 30 minutes does NOT start a new session). For each user, calculate every session's total `credits_used`, and determine: - their total number of sessions - their single highest-spending session's total credits Only include users who have **at least 2 sessions** (this intentionally excludes users who ran one long continuous session, no matter how many credits it consumed). Among qualifying users, rank them by their highest-spending session's total credits (highest first). Output only users in the **top 3 ranks**, and if multiple users tie for a rank, include all of them (which may produce more than 3 rows). Output the username, their highest single-session credit total, their total session count, and their rank. ### Table Schema | Table | Column | Type | |---|---|---| | `query_log` | `query_id` | integer | | `query_log` | `user_id` | integer | | `query_log` | `username` | string | | `query_log` | `query_time` | timestamp | | `query_log` | `credits_used` | decimal | ### Example Input: `query_log` | query_id | user_id | username | query_time | credits_used | |---|---|---|---|---| | 1 | 1 | alice | 2023-06-01 09:00:00 | 2.00 | | 2 | 1 | alice | 2023-06-01 09:10:00 | 3.00 | | 3 | 1 | alice | 2023-06-01 09:45:00 | 5.00 | | 4 | 1 | alice | 2023-06-01 09:50:00 | 4.00 | | 5 | 2 | bob | 2023-06-01 10:00:00 | 1.50 | ### Example Output Explanation Alice's queries at 09:00 and 09:10 are only 10 minutes apart, forming Session 1 (5.00 credits). Her next query at 09:45 is 35 minutes after 09:10, exceeding the 30-minute threshold, so it starts Session 2 along with the 09:50 query (9.00 credits). Alice has 2 sessions, so she qualifies, with a highest single-session total of 9.00 credits. Bob has only one query, meaning only one session, so he is excluded entirely regardless of his spend. Alice is therefore the only qualifying user and ranks #1.
DROP TABLE IF EXISTS query_log; CREATE TABLE query_log (query_id INT, user_id INT, username VARCHAR(100), query_time TIMESTAMP, credits_used DECIMAL(10,2)); INSERT INTO query_log VALUES (1, 1, 'alice', '2023-06-01 09:00:00', 2.00), (2, 1, 'alice', '2023-06-01 09:10:00', 3.00), (3, 1, 'alice', '2023-06-01 09:45:00', 5.00), (4, 1, 'alice', '2023-06-01 09:50:00', 4.00), (5, 2, 'bob', '2023-06-01 10:00:00', 1.50);