SQLize Online / PHPize Online  /  SQLtest Online

-- Hint: use Ctrl+Enter for SQL autocomplete CREATE TABLE design_exported ( event_id TEXT, timestamp BIGINT , user_id TEXT, design_category TEXT ); INSERT INTO design_exported (event_id, timestamp, user_id, design_category) VALUES ('a',1625731913,'z','animals'), ('b',1557866863,'y','animals'), ('c',1557866863,'x','plants'), ('c',1557866863,'x','plants'), ('c',1557866863,'a','plants'), ('e',1557866863,'b','plants'), ('g',1557866863,'c','plants'), ('g',1557866863,'d','plants'), ('h',1557866863,'e','plants'), ('i',1557866863,'f','plants'), ('j',1557866863,'g','plants'), ('r',1625731913,'g','potato'), ('e',1625731913,'x','potato') ; -- Calculate the distinct number of events per user over the last 7 days -- Calculate the decile for the number of events per user over the last 7 days -- Set up the common table expressions -- Table to extract the number of events for each user over the last 7 days WITH num_event AS ( SELECT user_id, -- Use distinct to avoid counting duplicates COUNT(DISTINCT event_id) AS event_count FROM design_exported -- Filter data to include only the previous 7 days from query at day level granularity WHERE to_timestamp(timestamp)::date >= now()::date - 7 GROUP BY user_id), -- Table to extract the last exported design category per user who exported in the last 7 days last_design AS ( SELECT DISTINCT * FROM( SELECT user_id, timestamp, design_category, -- window function to order rows by date ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY timestamp DESC) AS row_index FROM design_exported -- Filter data to include only the previous 7 days from query at day level granularity WHERE to_timestamp(timestamp)::date >= now()::date - 7) sub WHERE row_index = 1) SELECT num_event.user_id, -- Decile calculation NTILE(10) OVER(ORDER BY event_count) AS decile_event_count, design_category AS last_exported_design_category FROM num_event -- Joining the above two common expression tables based on the user_id JOIN last_design ON num_event.user_id = last_design.user_id