user_sessions +---------+------------+------+---------------------+---------------------+ | user_id | session_id | app | start_time | end_time | +---------+------------+------+---------------------+---------------------+ | 123 | 1 | ins | 2023-07-04 10:00:00 | 2023-07-04 10:15:42 | | 123 | 2 | fb | 2023-07-04 10:20:00 | 2023-07-04 10:35:10 | | 456 | 3 | ins | 2023-07-04 11:00:00 | 2023-07-04 11:02:07 | | 789 | 4 | fb | 2023-07-04 12:00:00 | 2023-07-04 12:45:00 | | 123 | 5 | ins | 2023-07-05 09:00:00 | 2023-07-05 09:25:30 | +---------+------------+------+---------------------+---------------------+ ​ ads_stats +--------------+------------------+---------+---------+------------+ | advertiser_id | creation_source | country | spend | date | +--------------+------------------+---------+---------+------------+ | 101 | web | US | 1500.00 | 2023-06-15 | | 102 | api | CA | 200.00 | 2023-06-15 | | 103 | mobile | IN | 5000.00 | 2023-07-01 | | 104 | web | UK | 750.00 | 2022-07-01 | | 105 | api | US | 1200.00 | 2023-07-02 | +--------------+------------------+---------+---------+------------+ ##### Scenario SQL data-manipulation round covering user session analytics and advertiser revenue questions. ##### Question Using yesterday’s data, compute the average session duration (end_time ‑ start_time) grouped by app. Define a performance metric for each app, calculate it, and decide which app performs best. For every day and app, calculate the bounce rate where a user switches to another app then returns to the first. Ads tasks: i) For each creation_source, report daily revenue for the past month. ii) Find the top 10 least-active advertisers and list their countries. iii) For every creation_source, compare this-year vs. last-year ratio of advertisers spending > 1000. iv) Show how to prove a revenue increase from one source is due to decreases in others. ##### Hints Use date filtering, TIMESTAMPDIFF, window functions, self-joins for bounces, CTEs, and careful denominator selection for ratios.