LeetCode SQL Medium

Currently Playing: Leetcode MEDIUM 3056 - Snaps Analysis - Solved by Everyday Data Science | CASE WHEN ROUND() SUM()

Question:https://leetcode.com/problems/snaps-analysis/description/ SQL Schema: Create table if Not Exists Activities(activity_id int, user_id int, activity_type ENUM('send', 'open'), time_spent decimal(5,2)) Create table if not Exists Age( user_id int, age_bucket ENUM('21-25','26-30','31-35')) Truncate table Activities insert into Activities (activity_id, user_id, activity_type, time_spent) values ('7274', '123', 'open', '4.5') insert into Activities (activity_id, user_id, activity_type, time_spent) values ('2425', '123', 'send', '3.5') insert into Activities (activity_id, user_id, activity_type, time_spent) values ('1413', '456', 'send', '5.67') insert into Activities (activity_id, user_id, activity_type, time_spent) values ('2536', '456', 'open', '3.0') insert into Activities (activity_id, user_id, activity_type, time_spent) values ('8564', '456', 'send', '8.24') insert into Activities (activity_id, user_id, activity_type, time_spent) values ('5235', '789', 'send', '6.24') insert into Activities (activity_id, user_id, activity_type, time_spent) values ('4251', '123', 'open', '1.25') insert into Activities (activity_id, user_id, activity_type, time_spent) values ('1435', '789', 'open', '5.25') Truncate table Age insert into Age (user_id, age_bucket) values ('123', '31-35') insert into Age (user_id, age_bucket) values ('789', '21-25') insert into Age (user_id, age_bucket) values ('456', '26-30') Pandas Schema: data = [[7274, 123, 'open', 4.5], [2425, 123, 'send', 3.5], [1413, 456, 'send', 5.67], [2536, 456, 'open', 3.0], [8564, 456, 'send', 8.24], [5235, 789, 'send', 6.24], [4251, 123, 'open', 1.25], [1435, 789, 'open', 5.25]] activities = pd.DataFrame(data, columns=['activity_id', 'user_id', 'activity_type', 'time_spent']).astype({'activity_id':'Int64', 'user_id':'Int64', 'activity_type':'object', 'time_spent':'Float64'}) data = [[123, '31-35'], [789, '21-25'], [456, '26-30']] age = pd.DataFrame(data, columns=['user_id', 'age_bucket']).astype({'user_id':'Int64', 'age_bucket':'object'}) #datasciencequestions #leetcodesolutions #dataengineering