22 Sending vs Opening Snaps Snapchat SQL Interview Question скачать в хорошем качестве

22 Sending vs Opening Snaps Snapchat SQL Interview Question

--Assume you're given tables with information on Snapchat users, --including their ages and time spent sending and opening snaps. --Write a query to obtain a breakdown of the time spent sending vs. opening snaps --as a percentage of total time spent on these activities grouped by age group. --Round the percentage to 2 decimal places in the output. --Notes: --total time spent by = (Time spent sending + Time spent opening) --Calculate the following percentages: --time spent sending / (Time spent sending + Time spent opening) --Time spent opening / (Time spent sending + Time spent opening) --To avoid integer division in percentages, multiply by 100.0 and not 100. --Effective April 15th, 2023, the solution has been updated and optimised. CREATE TABLE ACTIVITIES ( ACTIVITY_ID INT, USER_ID INT, ACTIVITY_TYPE VARCHAR(10), TIME_SPENT DECIMAL(5,2), ACTIVITY_DATE DATETIME2 ) --INSERT INTO ACTIVITIES VALUES(7274,123,'open',4.5,'06/22/2022 12:00:00') --INSERT INTO ACTIVITIES VALUES(2425,123,'send',3.5,'06/22/2022 12:00:00') --INSERT INTO ACTIVITIES VALUES(1413,456,'send',5.67,'06/23/2022 12:00:00') --INSERT INTO ACTIVITIES VALUES(2536,456,'open',3,'06/25/2022 12:00:00') --INSERT INTO ACTIVITIES VALUES(8564,456,'send',8.24,'06/26/2022 12:00:00') --INSERT INTO ACTIVITIES VALUES(5235,789,'send',6.24,'06/28/2022 12:00:00') --INSERT INTO ACTIVITIES VALUES(4251,123,'open',1.25,'07-01-2022 12:00:00') --INSERT INTO ACTIVITIES VALUES(1414,789,'chat',11,'06/25/2022 12:00:00') --INSERT INTO ACTIVITIES VALUES(1314,123,'chat',3.15,'06/26/2022 12:00:00') --INSERT INTO ACTIVITIES VALUES(1435,789,'open',5.25,'07-02-2022 12:00:00') CREATE TABLE AGE_BREAKDOWN ( USER_ID INT, AGE_BUCKET VARCHAR(10) ) --INSERT INTO AGE_BREAKDOWN VALUES(123,'31-35') --INSERT INTO AGE_BREAKDOWN VALUES(456,'26-30') --INSERT INTO AGE_BREAKDOWN VALUES(789,'21-25')