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')