SQL_DS_collections, Sql Server

SQL_DS_collections

/*
CREATE TABLE Activity
    ([user_id] int, [page] varchar(25), [unix_timestamp] int)
;
    
INSERT INTO Activity
    ([user_id], [page], [unix_timestamp])
VALUES
    (2, 'home_page', 1451606400),
    (3, 'home_page', 1451606402),
    (3, 'search', 1451606404),
    (4, 'home_page', 1451606407),
    (4, 'payment_page', 1451606423),
    (4, 'product_page', 1451606419),
    (4, 'search', 1451606418),
    (5, 'home_page', 1451606425),
    (7, 'home_page', 1451606427),
    (8, 'home_page', 1451606437),
    (9, 'home_page', 1451606439),
    (13, 'home_page', 1451606441),
    (14, 'home_page', 1451606449),
    (15, 'search', 1451606465),
    (15, 'home_page', 1451606458),
    (17, 'home_page', 1451606470),
    (21, 'home_page', 1451606480),
    (22, 'home_page', 1451606484),
    (23, 'home_page', 1451606488),
    (24, 'home_page', 1451606498),
    (24, 'search', 1451606504),
    (25, 'home_page', 1451606509),
    (26, 'home_page', 1451606511),
    (27, 'home_page', 1451606521),
    (27, 'search', 1451606522),
    (28, 'home_page', 1451606524),
    (29, 'home_page', 1451606530),
    (30, 'home_page', 1451606540),
    (31, 'home_page', 1451606545),
    (33, 'home_page', 1451606546),
    (37, 'home_page', 1451606548),
    (39, 'home_page', 1451606549),
    (42, 'home_page', 1451606550),
    (43, 'home_page', 1451606552),
    (46, 'home_page', 1451606553),
    (47, 'home_page', 1451606556),
    (51, 'home_page', 1451606574),
    (51, 'payment_page', 1451606590),
    (51, 'search', 1451606575),
    (51, 'product_page', 1451606582),
    (52, 'search', 1451606596),
    (52, 'home_page', 1451606595),
    (53, 'home_page', 1451606601),
    (54, 'home_page', 1451606602),
    (55, 'home_page', 1451606612),
    (55, 'search', 1451606613),
    (57, 'home_page', 1451606615),
    (60, 'home_page', 1451606616),
    (60, 'search', 1451606617),
    (62, 'home_page', 1451606621),
    (63, 'home_page', 1451606622),
    (64, 'search', 1451606629),
    (64, 'home_page', 1451606624),
    (66, 'product_page', 1451606632),
    (66, 'home_page', 1451606630),
    (66, 'search', 1451606631),
    (69, 'home_page', 1451606633),
    (70, 'search', 1451606636),
    (70, 'home_page', 1451606634),
    (71, 'home_page', 1451606639),
    (72, 'home_page', 1451606642),
    (73, 'home_page', 1451606646),
    (76, 'search', 1451606657),
    (76, 'home_page', 1451606655),
    (77, 'search', 1451606670),
    (77, 'home_page', 1451606667),
    (78, 'product_page', 1451606691),
    (78, 'search', 1451606684),
    (78, 'payment_confirmation_page', 1451606704),
    (78, 'home_page', 1451606677),
    (78, 'payment_page', 1451606694),
    (79, 'home_page', 1451606705),
    (81, 'search', 1451606713),
    (81, 'home_page', 1451606712),
    (81, 'payment_page', 1451606721),
    (81, 'product_page', 1451606716),
    (82, 'home_page', 1451606726),
    (82, 'product_page', 1451606732),
    (82, 'search', 1451606730),
    (83, 'home_page', 1451606735),
    (84, 'home_page', 1451606736),
    (86, 'home_page', 1451606750),
    (87, 'home_page', 1451606755),
    (87, 'product_page', 1451606764),
    (87, 'search', 1451606759),
    (89, 'home_page', 1451606766),
    (90, 'home_page', 1451606773),
    (92, 'home_page', 1451606778),
    (92, 'search', 1451606786),
    (93, 'home_page', 1451606790),
    (95, 'home_page', 1451606792),
    (96, 'home_page', 1451606793),
    (96, 'search', 1451606802),
    (96, 'payment_page', 1451606807),
    (96, 'product_page', 1451606806),
    (97, 'search', 1451606831),
    (97, 'product_page', 1451606835),
    (97, 'home_page', 1451606809),
    (98, 'home_page', 1451606837),
    (100, 'home_page', 1451606842),
    (100, 'search', 1451606845),
    (103, 'home_page', 1451606846),
    (106, 'home_page', 1451606851),
    (108, 'home_page', 1451606854),
    (111, 'search', 1451606860),
    (111, 'home_page', 1451606856),
    (112, 'home_page', 1451606867),
    (113, 'home_page', 1451606871),
    (114, 'search', 1451606883),
    (114, 'home_page', 1451606881),
    (115, 'home_page', 1451606894),
    (115, 'search', 1451606895),
    (115, 'payment_page', 1451606907),
    (115, 'product_page', 1451606906),
    (116, 'home_page', 1451606909),
    (118, 'home_page', 1451606913),
    (119, 'home_page', 1451606926),
    (120, 'search', 1451606931),
    (120, 'home_page', 1451606930),
    (122, 'home_page', 1451606936),
    (123, 'home_page', 1451606939),
    (124, 'home_page', 1451606945),
    (125, 'home_page', 1451606975),
    (125, 'search', 1451606977),
    (126, 'home_page', 1451606978),
    (126, 'search', 1451606979),
    (127, 'home_page', 1451606999),
    (128, 'home_page', 1451607004),
    (130, 'home_page', 1451607006),
    (130, 'search', 1451607007),
    (131, 'home_page', 1451607013),
    (133, 'home_page', 1451607016),
    (134, 'search', 1451607032),
    (134, 'home_page', 1451607021),
    (135, 'home_page', 1451607033),
    (135, 'search', 1451607046),
    (136, 'search', 1451607055),
    (136, 'product_page', 1451607057),
    (136, 'home_page', 1451607050),
    (140, 'home_page', 1451607058),
    (142, 'home_page', 1451607068),
    (143, 'home_page', 1451607070),
    (144, 'home_page', 1451607072),
    (145, 'home_page', 1451607076),
    (145, 'search', 1451607091),
    (145, 'product_page', 1451607115),
    (148, 'home_page', 1451607120),
    (150, 'search', 1451607123),
    (150, 'home_page', 1451607121),
    (151, 'home_page', 1451607125),
    (152, 'home_page', 1451607129),
    (153, 'home_page', 1451607147),
    (154, 'home_page', 1451607149),
    (156, 'home_page', 1451607151),
    (159, 'search', 1451607157),
    (159, 'home_page', 1451607154),
    (161, 'home_page', 1451607163),
    (163, 'home_page', 1451607165),
    (163, 'product_page', 1451607174),
    (163, 'payment_page', 1451607185),
    (163, 'search', 1451607173),
    (164, 'home_page', 1451607196),
    (167, 'search', 1451607217),
    (167, 'home_page', 1451607200),
    (168, 'home_page', 1451607221),
    (168, 'search', 1451607239),
    (169, 'home_page', 1451607242),
    (170, 'home_page', 1451607261),
    (171, 'home_page', 1451607265),
    (173, 'search', 1451607286),
    (173, 'home_page', 1451607272),
    (173, 'product_page', 1451607288),
    (177, 'home_page', 1451607290),
    (179, 'home_page', 1451607293),
    (181, 'home_page', 1451607309),
    (183, 'home_page', 1451607310),
    (183, 'search', 1451607311),
    (185, 'home_page', 1451607317),
    (186, 'home_page', 1451607319),
    (187, 'home_page', 1451607323),
    (187, 'search', 1451607325),
    (188, 'home_page', 1451607329),
    (191, 'home_page', 1451607332),
    (192, 'home_page', 1451607335),
    (193, 'home_page', 1451607340),
    (194, 'home_page', 1451607342),
    (195, 'search', 1451607358),
    (195, 'home_page', 1451607349),
    (198, 'home_page', 1451607368),
    (200, 'home_page', 1451607380),
    (200, 'search', 1451607387),
    (201, 'home_page', 1451607393),
    (202, 'home_page', 1451607395),
    (204, 'home_page', 1451607396),
    (207, 'home_page', 1451607401),
    (208, 'home_page', 1451607404),
    (212, 'home_page', 1451607409),
    (213, 'home_page', 1451607411),
    (217, 'home_page', 1451607421),
    (217, 'search', 1451607426)
;

-- For each user_id, find the difference between the last action and the second last action. 
-- Action here is defined as visiting a page. If the user has just one action, you can either remove her from the final results or 
-- keep that user_id and have NULL as time difference between the two actions.

----------------- sol1 ---------------------
WITH ActivityRank AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY unix_timestamp DESC) AS time_rank
--    LAG(unix_timestamp,1) OVER(PARTITION BY user_id ORDER BY unix_timestamp DESC) AS lag
    FROM Activity),
    LastTwoActivities AS (
        SELECT *
        FROM ActivityRank
        WHERE time_rank<=2)
 SELECT a.user_id, a.unix_timestamp - b.unix_timestamp AS time_diff
 FROM LastTwoActivities AS a, LastTwoActivities AS b
 WHERE a.user_id = b.user_id AND a.time_rank+1 = b.time_rank;

------------- sol2 ------------------

WITH ActivityRank AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY unix_timestamp DESC) AS time_rank,
    LAG(unix_timestamp,1) OVER(PARTITION BY user_id ORDER BY unix_timestamp) AS lag1_time
    FROM Activity),
    LastTwoActivities AS (
        SELECT *
        FROM ActivityRank
        WHERE time_rank=1)
 SELECT user_id, unix_timestamp - lag1_time AS time_diff
 FROM LastTwoActivities 
*/
---------------------------------
------------------ Q2 --------------------
---------------------------------

CREATE TABLE Mobile
    ([user_id] int, [page] varchar(13))
;
    
INSERT INTO Mobile
    ([user_id], [page])
VALUES
    (1, 'page_3_mobile'),
    (1, 'page_2_mobile'),
    (2, 'page_7_mobile'),
    (4, 'page_2_mobile'),
    (5, 'page_3_mobile'),
    (7, 'page_4_mobile'),
    (7, 'page_4_mobile'),
    (8, 'page_3_mobile'),
    (9, 'page_4_mobile'),
    (9, 'page_8_mobile'),
    (10, 'page_1_mobile'),
    (12, 'page_8_mobile'),
    (12, 'page_7_mobile'),
    (12, 'page_1_mobile'),
    (13, 'page_3_mobile'),
    (14, 'page_1_mobile'),
    (14, 'page_4_mobile'),
    (16, 'page_8_mobile'),
    (16, 'page_8_mobile'),
    (16, 'page_5_mobile'),
    (17, 'page_2_mobile'),
    (17, 'page_8_mobile'),
    (17, 'page_2_mobile'),
    (18, 'page_6_mobile'),
    (18, 'page_8_mobile'),
    (19, 'page_8_mobile'),
    (20, 'page_4_mobile'),
    (21, 'page_6_mobile'),
    (23, 'page_1_mobile'),
    (24, 'page_1_mobile'),
    (24, 'page_4_mobile'),
    (25, 'page_5_mobile'),
    (25, 'page_8_mobile'),
    (26, 'page_7_mobile'),
    (26, 'page_1_mobile'),
    (26, 'page_4_mobile'),
    (27, 'page_6_mobile'),
    (29, 'page_3_mobile'),
    (29, 'page_5_mobile'),
    (30, 'page_4_mobile'),
    (33, 'page_1_mobile'),
    (33, 'page_1_mobile'),
    (34, 'page_7_mobile'),
    (34, 'page_3_mobile'),
    (34, 'page_5_mobile'),
    (34, 'page_3_mobile'),
    (34, 'page_5_mobile'),
    (35, 'page_8_mobile'),
    (35, 'page_2_mobile'),
    (35, 'page_3_mobile'),
    (36, 'page_7_mobile'),
    (36, 'page_7_mobile'),
    (37, 'page_6_mobile'),
    (38, 'page_2_mobile'),
    (38, 'page_5_mobile'),
    (39, 'page_6_mobile')
;

CREATE TABLE Web
    ([user_id] int, [page] varchar(10))
;
    
INSERT INTO Web
    ([user_id], [page])
VALUES
    (1, 'page_8_web'),
    (4, 'page_5_web'),
    (4, 'page_6_web'),
    (5, 'page_5_web'),
    (6, 'page_3_web'),
    (9, 'page_5_web'),
    (11, 'page_7_web'),
    (13, 'page_6_web'),
    (13, 'page_7_web'),
    (15, 'page_3_web'),
    (15, 'page_6_web'),
    (16, 'page_1_web'),
    (17, 'page_6_web'),
    (17, 'page_3_web'),
    (18, 'page_2_web'),
    (22, 'page_2_web'),
    (23, 'page_6_web'),
    (24, 'page_4_web'),
    (26, 'page_4_web'),
    (26, 'page_5_web'),
    (27, 'page_6_web'),
    (28, 'page_5_web'),
    (28, 'page_7_web'),
    (30, 'page_1_web'),
    (31, 'page_4_web'),
    (31, 'page_7_web'),
    (32, 'page_3_web'),
    (33, 'page_8_web'),
    (35, 'page_6_web'),
    (35, 'page_1_web'),
    (36, 'page_1_web'),
    (36, 'page_2_web'),
    (36, 'page_8_web'),
    (37, 'page_2_web'),
    (37, 'page_2_web'),
    (38, 'page_7_web'),
    (38, 'page_5_web')
;
-- We have two tables. One table has all mobile actions, i.e. all pages visited by the users on mobile. 
-- The other table has all web actions, i.e. all pages visited on web by the users.
-- Write a query that returns the percentage of users who only visited mobile, only web and both. That is, the percentage of users who are only in the mobile table, only in the web table and in both tables. The sum of the percentages should return 1.
/*

------- sol1 -----------
WITH mobile_only_cnt AS (
    SELECT COUNT(DISTINCT user_id) AS users
    FROM Mobile
    WHERE user_id NOT IN (SELECT user_id FROM Web)),
    web_only_cnt AS (     
    SELECT COUNT(user_id) AS users
    FROM Web
    WHERE user_id NOT IN (SELECT user_id FROM Mobile)),
    both_cnt AS (
    SELECT COUNT(DISTINCT a.user_id) AS users
    FROM Mobile AS a INNER JOIN Web AS b ON a.user_id = b.user_id),
union_cnt AS (
    SELECT COUNT(DISTINCT user_id) AS users
    FROM (SELECT user_id 
          FROM Web 
          UNION 
          SELECT user_id 
          FROM Mobile) AS t)                    
SELECT a.users*1.00/b.users AS mobile, c.users*1.00/b.users AS web, d.users*1.00/b.users AS both
FROM mobile_only_cnt AS a CROSS JOIN union_cnt AS b CROSS JOIN web_only_cnt AS c CROSS JOIN both_cnt AS d

--------- sol2 ------------
SELECT SUM(CASE WHEN a.user_id IS NULL THEN 1 ELSE 0 END)*1.00/COUNT(*) AS web_only,
SUM(CASE WHEN b.user_id IS NULL THEN 1 ELSE 0 END)*1.00/COUNT(*) AS mobile_only,
SUM(CASE WHEN a.user_id IS NOT NULL AND b.user_id IS NOT NULL THEN 1 ELSE 0 END)*1.00/COUNT(*) AS both
FROM
(SELECT user_id
 FROM Mobile
 GROUP BY user_id) AS a FULL OUTER JOIN 
(SELECT user_id
 FROM Web
 GROUP BY user_id) AS b
ON a.user_id = b.user_id

*/
------------------------------------------
------------------ Q3 -------------------
-------------------------------------------

CREATE TABLE Purchases
    ([user_id] int, [date] datetime)
;
    
INSERT INTO Purchases
    ([user_id], [date])
VALUES
    (1, '2015-01-31 21:37:00'),
    (1, '2015-02-27 02:53:00'),
    (1, '2015-04-06 02:54:00'),
    (1, '2015-04-15 05:30:00'),
    (1, '2015-05-01 18:21:00'),
    (1, '2015-05-08 10:44:00'),
    (1, '2015-05-30 00:08:00'),
    (1, '2015-09-20 17:09:00'),
    (1, '2015-10-21 01:41:00'),
    (1, '2015-10-21 06:20:00'),
    (1, '2015-12-15 14:27:00'),
    (1, '2015-12-16 16:16:00'),
    (2, '2015-01-03 23:44:00'),
    (2, '2015-01-08 09:05:00'),
    (2, '2015-05-06 02:40:00'),
    (2, '2015-06-01 00:42:00'),
    (2, '2015-08-20 02:14:00'),
    (2, '2015-10-06 06:54:00'),
    (2, '2015-10-06 15:32:00'),
    (2, '2015-11-01 07:23:00'),
    (2, '2015-11-04 16:56:00'),
    (3, '2015-04-01 15:31:00'),
    (3, '2015-04-07 11:59:00'),
    (3, '2015-06-08 16:34:00'),
    (3, '2015-07-28 00:16:00'),
    (3, '2015-09-09 20:36:00'),
    (3, '2015-09-26 03:57:00'),
    (3, '2015-10-03 17:14:00'),
    (3, '2015-10-17 09:07:00'),
    (3, '2015-10-23 13:32:00'),
    (3, '2015-10-29 22:41:00'),
    (3, '2015-11-04 14:01:00'),
    (3, '2015-12-02 00:56:00'),
    (3, '2015-12-15 17:17:00'),
    (3, '2015-12-22 04:03:00'),
    (4, '2015-01-15 12:34:00'),
    (4, '2015-04-15 02:13:00'),
    (4, '2015-05-18 04:28:00'),
    (4, '2015-05-21 21:17:00'),
    (4, '2015-05-22 20:45:00'),
    (4, '2015-06-10 02:55:00'),
    (4, '2015-06-27 15:31:00'),
    (4, '2015-09-13 04:00:00'),
    (4, '2015-09-20 02:44:00'),
    (4, '2015-09-25 12:36:00'),
    (4, '2015-11-19 06:33:00'),
    (5, '2015-01-13 20:00:00'),
    (5, '2015-02-09 18:41:00'),
    (5, '2015-04-09 02:39:00'),
    (5, '2015-04-26 05:26:00'),
    (5, '2015-05-02 20:53:00'),
    (5, '2015-05-05 08:25:00'),
    (5, '2015-05-19 10:55:00'),
    (5, '2015-08-25 03:30:00'),
    (5, '2015-09-03 16:06:00'),
    (5, '2015-09-18 06:25:00'),
    (5, '2015-11-15 11:19:00'),
    (5, '2015-11-18 12:34:00'),
    (5, '2015-12-26 03:52:00'),
    (6, '2015-02-16 11:48:00'),
    (6, '2015-02-16 13:35:00'),
    (6, '2015-02-24 15:44:00'),
    (6, '2015-03-03 10:04:00'),
    (6, '2015-07-06 11:50:00'),
    (6, '2015-08-14 10:49:00'),
    (6, '2015-09-11 04:38:00'),
    (6, '2015-11-03 00:53:00'),
    (6, '2015-11-13 18:27:00'),
    (6, '2015-12-30 00:53:00'),
    (7, '2015-01-02 13:34:00'),
    (7, '2015-01-18 03:53:00'),
    (7, '2015-01-20 16:10:00'),
    (7, '2015-02-27 03:08:00'),
    (7, '2015-03-15 08:49:00'),
    (7, '2015-04-28 10:19:00'),
    (7, '2015-05-25 13:00:00'),
    (7, '2015-06-11 10:36:00'),
    (7, '2015-06-28 12:29:00'),
    (7, '2015-09-10 02:46:00'),
    (7, '2015-10-01 19:31:00'),
    (7, '2015-10-31 14:05:00'),
    (7, '2015-12-19 23:13:00'),
    (7, '2015-12-26 07:20:00'),
    (8, '2015-05-14 08:23:00'),
    (8, '2015-07-24 12:33:00'),
    (8, '2015-11-09 15:14:00'),
    (8, '2015-12-10 20:53:00'),
    (9, '2015-01-23 00:15:00'),
    (9, '2015-04-17 22:54:00'),
    (9, '2015-06-25 02:14:00'),
    (9, '2015-10-16 15:03:00'),
    (9, '2015-10-23 16:33:00'),
    (10, '2015-01-31 02:39:00'),
    (10, '2015-04-24 01:56:00'),
    (10, '2015-06-21 13:18:00'),
    (10, '2015-07-22 08:58:00'),
    (10, '2015-08-13 20:29:00'),
    (10, '2015-08-23 09:02:00'),
    (10, '2015-10-21 20:52:00'),
    (10, '2015-11-05 20:48:00'),
    (10, '2015-12-21 01:09:00'),
    (10, '2015-12-24 12:52:00'),
    (11, '2015-02-12 21:35:00'),
    (11, '2015-05-09 14:40:00'),
    (11, '2015-05-15 05:32:00'),
    (11, '2015-05-19 12:19:00'),
    (11, '2015-05-31 18:49:00'),
    (11, '2015-06-22 12:38:00'),
    (11, '2015-09-17 12:26:00'),
    (11, '2015-09-19 06:10:00'),
    (11, '2015-10-29 21:33:00'),
    (11, '2015-11-10 05:31:00'),
    (11, '2015-12-29 12:13:00'),
    (12, '2015-01-08 06:27:00'),
    (12, '2015-01-09 04:12:00'),
    (12, '2015-01-18 09:40:00'),
    (12, '2015-02-18 13:10:00'),
    (12, '2015-04-14 23:39:00'),
    (12, '2015-04-21 17:23:00'),
    (12, '2015-06-23 04:08:00'),
    (12, '2015-07-22 18:35:00'),
    (12, '2015-07-27 19:01:00'),
    (12, '2015-10-01 20:24:00'),
    (12, '2015-10-08 10:51:00'),
    (12, '2015-10-14 16:56:00'),
    (12, '2015-10-15 08:39:00'),
    (12, '2015-10-21 07:24:00'),
    (12, '2015-11-12 11:16:00'),
    (13, '2015-01-05 17:26:00'),
    (13, '2015-03-30 00:55:00'),
    (13, '2015-05-15 10:47:00'),
    (13, '2015-06-13 18:56:00'),
    (13, '2015-06-21 10:07:00'),
    (13, '2015-12-15 05:45:00'),
    (14, '2015-01-31 18:07:00'),
    (14, '2015-06-07 18:40:00'),
    (14, '2015-08-08 07:28:00'),
    (14, '2015-08-30 12:08:00'),
    (14, '2015-09-10 00:34:00'),
    (14, '2015-10-30 01:52:00'),
    (14, '2015-12-01 10:54:00'),
    (14, '2015-12-15 16:56:00'),
    (14, '2015-12-16 08:18:00'),
    (15, '2015-01-12 14:39:00'),
    (15, '2015-02-13 23:08:00'),
    (15, '2015-03-14 23:12:00'),
    (15, '2015-04-19 02:12:00'),
    (15, '2015-05-07 15:39:00'),
    (15, '2015-06-29 11:01:00'),
    (15, '2015-10-28 07:27:00'),
    (16, '2015-01-16 09:36:00'),
    (16, '2015-03-11 22:35:00'),
    (16, '2015-04-08 22:12:00'),
    (16, '2015-04-22 13:06:00'),
    (16, '2015-04-29 08:19:00'),
    (16, '2015-07-09 07:18:00'),
    (16, '2015-09-10 13:02:00'),
    (16, '2015-10-08 19:12:00'),
    (16, '2015-10-10 23:11:00'),
    (16, '2015-11-14 08:01:00'),
    (16, '2015-12-15 00:58:00'),
    (17, '2015-01-23 20:51:00'),
    (17, '2015-02-19 16:25:00'),
    (17, '2015-03-12 09:07:00'),
    (17, '2015-03-29 09:19:00'),
    (17, '2015-04-14 09:31:00'),
    (17, '2015-06-11 07:22:00'),
    (17, '2015-06-15 07:01:00'),
    (17, '2015-07-14 14:55:00'),
    (17, '2015-08-22 07:10:00'),
    (17, '2015-10-03 23:32:00'),
    (17, '2015-11-29 21:45:00'),
    (18, '2015-02-10 20:00:00'),
    (18, '2015-02-13 00:26:00'),
    (18, '2015-03-17 05:46:00'),
    (18, '2015-03-18 20:20:00'),
    (18, '2015-06-01 00:06:00'),
    (18, '2015-06-04 21:56:00'),
    (18, '2015-07-22 10:04:00'),
    (18, '2015-07-25 04:39:00'),
    (18, '2015-09-28 03:36:00'),
    (18, '2015-10-02 20:05:00'),
    (18, '2015-10-07 01:01:00'),
    (18, '2015-10-26 06:47:00'),
    (18, '2015-11-17 02:40:00'),
    (19, '2015-02-16 22:30:00'),
    (19, '2015-04-16 13:06:00'),
    (19, '2015-04-25 12:35:00'),
    (19, '2015-05-24 12:01:00'),
    (19, '2015-07-16 15:56:00'),
    (19, '2015-07-23 05:00:00'),
    (19, '2015-07-25 17:29:00'),
    (19, '2015-09-04 23:56:00'),
    (19, '2015-09-08 07:18:00'),
    (19, '2015-10-11 17:44:00'),
    (19, '2015-11-18 03:16:00'),
    (19, '2015-12-25 01:41:00'),
    (19, '2015-12-26 16:18:00'),
    (20, '2015-01-19 23:36:00'),
    (20, '2015-03-08 17:41:00'),
    (20, '2015-07-08 16:36:00'),
    (20, '2015-08-01 14:45:00'),
    (20, '2015-09-05 03:52:00'),
    (20, '2015-09-10 03:42:00'),
    (20, '2015-10-13 15:10:00'),
    (20, '2015-10-26 07:55:00'),
    (20, '2015-12-03 10:50:00'),
    (21, '2015-01-05 03:02:00'),
    (21, '2015-04-14 18:13:00'),
    (21, '2015-04-18 00:51:00'),
    (21, '2015-04-21 21:53:00'),
    (21, '2015-06-14 11:59:00'),
    (21, '2015-06-19 05:16:00'),
    (21, '2015-07-21 17:47:00'),
    (21, '2015-08-28 04:25:00'),
    (21, '2015-08-31 19:38:00'),
    (21, '2015-10-26 18:14:00'),
    (21, '2015-10-28 03:38:00'),
    (21, '2015-12-10 10:07:00'),
    (21, '2015-12-16 17:08:00'),
    (22, '2015-02-02 21:47:00'),
    (22, '2015-02-27 11:13:00'),
    (22, '2015-03-26 04:42:00'),
    (22, '2015-06-08 19:01:00'),
    (22, '2015-06-24 01:20:00'),
    (22, '2015-07-18 00:37:00'),
    (22, '2015-08-04 02:14:00'),
    (22, '2015-09-08 22:52:00'),
    (22, '2015-09-23 07:58:00'),
    (22, '2015-09-25 10:51:00'),
    (22, '2015-09-28 03:26:00'),
    (22, '2015-10-22 13:21:00'),
    (22, '2015-11-13 19:19:00'),
    (23, '2015-01-28 22:25:00'),
    (23, '2015-03-18 22:53:00'),
    (23, '2015-04-24 13:31:00'),
    (23, '2015-05-19 10:32:00'),
    (23, '2015-05-27 14:37:00'),
    (23, '2015-06-01 19:40:00'),
    (23, '2015-07-09 14:32:00'),
    (23, '2015-10-20 07:45:00'),
    (23, '2015-10-29 02:50:00'),
    (23, '2015-12-18 04:54:00'),
    (24, '2015-01-07 20:28:00'),
    (24, '2015-02-26 06:25:00'),
    (24, '2015-05-24 10:54:00'),
    (24, '2015-05-31 01:15:00'),
    (24, '2015-06-08 06:22:00'),
    (24, '2015-08-25 22:05:00'),
    (24, '2015-09-06 10:17:00'),
    (24, '2015-09-18 21:11:00'),
    (24, '2015-11-04 02:13:00'),
    (24, '2015-11-04 09:54:00'),
    (24, '2015-11-10 14:57:00'),
    (24, '2015-12-03 22:53:00'),
    (24, '2015-12-05 02:59:00'),
    (24, '2015-12-26 15:15:00'),
    (25, '2015-01-04 09:39:00'),
    (25, '2015-02-16 10:00:00'),
    (25, '2015-03-20 04:39:00'),
    (25, '2015-04-07 07:41:00'),
    (25, '2015-04-16 08:37:00'),
    (25, '2015-04-19 15:30:00'),
    (25, '2015-07-24 03:14:00'),
    (25, '2015-08-15 16:36:00'),
    (25, '2015-08-28 07:57:00'),
    (25, '2015-09-19 20:49:00'),
    (26, '2015-01-23 23:24:00'),
    (26, '2015-03-25 19:45:00'),
    (26, '2015-05-10 09:23:00'),
    (26, '2015-06-04 02:02:00'),
    (26, '2015-09-01 11:57:00'),
    (26, '2015-10-14 15:38:00'),
    (26, '2015-10-27 14:39:00'),
    (27, '2015-02-21 15:20:00'),
    (27, '2015-03-25 17:47:00'),
    (27, '2015-05-26 00:14:00'),
    (27, '2015-09-27 21:52:00'),
    (27, '2015-10-10 16:46:00'),
    (27, '2015-10-17 00:05:00'),
    (27, '2015-12-20 17:57:00'),
    (28, '2015-01-19 11:16:00'),
    (28, '2015-01-30 07:34:00'),
    (28, '2015-03-30 02:04:00'),
    (28, '2015-04-03 10:14:00'),
    (28, '2015-04-11 07:56:00'),
    (28, '2015-04-18 21:49:00'),
    (28, '2015-04-20 12:54:00'),
    (28, '2015-04-23 20:09:00'),
    (28, '2015-07-22 01:40:00'),
    (28, '2015-08-02 01:30:00'),
    (28, '2015-09-17 10:33:00'),
    (28, '2015-09-28 22:24:00'),
    (28, '2015-11-19 09:20:00'),
    (29, '2015-01-01 19:45:00'),
    (29, '2015-02-22 09:22:00'),
    (29, '2015-03-22 05:28:00'),
    (29, '2015-04-10 15:31:00'),
    (29, '2015-05-04 20:03:00'),
    (29, '2015-05-10 07:12:00'),
    (29, '2015-06-05 05:06:00'),
    (29, '2015-06-22 07:36:00'),
    (29, '2015-08-16 11:41:00'),
    (29, '2015-08-27 05:34:00'),
    (29, '2015-10-22 01:36:00'),
    (29, '2015-12-18 17:38:00'),
    (29, '2015-12-25 15:40:00'),
    (29, '2015-12-27 19:39:00'),
    (30, '2015-01-30 00:27:00'),
    (30, '2015-02-23 04:10:00'),
    (30, '2015-04-15 03:25:00'),
    (30, '2015-04-19 05:14:00'),
    (30, '2015-05-10 18:00:00'),
    (30, '2015-06-19 17:12:00'),
    (30, '2015-06-29 22:13:00'),
    (30, '2015-07-15 05:29:00'),
    (30, '2015-07-26 17:13:00'),
    (30, '2015-07-27 20:23:00'),
    (30, '2015-07-31 07:00:00'),
    (30, '2015-08-13 11:27:00'),
    (30, '2015-08-21 16:39:00'),
    (30, '2015-10-12 22:01:00'),
    (30, '2015-11-01 05:47:00'),
    (30, '2015-11-09 06:14:00'),
    (30, '2015-11-12 23:17:00'),
    (30, '2015-11-28 04:41:00'),
    (30, '2015-12-09 19:59:00')
;

-- We define as power users those users who bought at least 10 products. 
-- Write a query that returns for each user on which day they became a power user. That is, for each user, on which day they bought the 10th item.
-- The table below represents transactions. That is, each row means that the corresponding user has bought something on that date.

/*
SELECT user_id, date
FROM
(SELECT *,
ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY date) AS date_rank
FROM Purchases) AS t
WHERE date_rank=10
*/

-----------------------------------
---------------- Q4 --------------------
---------------------------------------

CREATE TABLE April
    ([user_id] int, [date] datetime, [transaction_amount] int)
;
    
INSERT INTO April
    ([user_id], [date], [transaction_amount])
VALUES
    (4, '2015-04-20 00:00:00', 93),
    (8, '2015-04-07 00:00:00', 96),
    (9, '2015-04-17 00:00:00', 89),
    (9, '2015-04-18 00:00:00', 70),
    (10, '2015-04-07 00:00:00', 15),
    (11, '2015-04-03 00:00:00', 71),
    (12, '2015-04-14 00:00:00', 79),
    (16, '2015-04-14 00:00:00', 60),
    (17, '2015-04-08 00:00:00', 92),
    (18, '2015-04-03 00:00:00', 54),
    (18, '2015-04-08 00:00:00', 23),
    (19, '2015-04-10 00:00:00', 22),
    (23, '2015-04-07 00:00:00', 33),
    (30, '2015-04-06 00:00:00', 50),
    (32, '2015-04-22 00:00:00', 43),
    (33, '2015-04-21 00:00:00', 45),
    (38, '2015-04-03 00:00:00', 142),
    (41, '2015-04-13 00:00:00', 70),
    (41, '2015-04-24 00:00:00', 34),
    (42, '2015-04-09 00:00:00', 110),
    (42, '2015-04-19 00:00:00', 94),
    (43, '2015-04-25 00:00:00', 22),
    (43, '2015-04-25 00:00:00', 38),
    (45, '2015-04-19 00:00:00', 61),
    (49, '2015-04-28 00:00:00', 60),
    (50, '2015-04-09 00:00:00', 77),
    (52, '2015-04-20 00:00:00', 42),
    (55, '2015-04-01 00:00:00', 103),
    (55, '2015-04-11 00:00:00', 125),
    (60, '2015-04-16 00:00:00', 84),
    (63, '2015-04-06 00:00:00', 67),
    (66, '2015-04-27 00:00:00', 68),
    (70, '2015-04-05 00:00:00', 28),
    (71, '2015-04-02 00:00:00', 65),
    (72, '2015-04-03 00:00:00', 49),
    (77, '2015-04-07 00:00:00', 54),
    (81, '2015-04-24 00:00:00', 67),
    (82, '2015-04-14 00:00:00', 39),
    (82, '2015-04-23 00:00:00', 46),
    (84, '2015-04-15 00:00:00', 92),
    (85, '2015-04-05 00:00:00', 70),
    (89, '2015-04-10 00:00:00', 18),
    (90, '2015-04-12 00:00:00', 92),
    (94, '2015-04-08 00:00:00', 58),
    (95, '2015-04-02 00:00:00', 76),
    (97, '2015-04-29 00:00:00', 53),
    (99, '2015-04-22 00:00:00', 49),
    (102, '2015-04-24 00:00:00', 72),
    (103, '2015-04-07 00:00:00', 77),
    (103, '2015-04-16 00:00:00', 121),
    (105, '2015-04-08 00:00:00', 57),
    (108, '2015-04-21 00:00:00', 102),
    (108, '2015-04-26 00:00:00', 38),
    (111, '2015-04-13 00:00:00', 70),
    (112, '2015-04-10 00:00:00', 67),
    (112, '2015-04-23 00:00:00', 15),
    (114, '2015-04-14 00:00:00', 68),
    (115, '2015-04-27 00:00:00', 62),
    (118, '2015-04-09 00:00:00', 37),
    (123, '2015-04-24 00:00:00', 79),
    (125, '2015-04-21 00:00:00', 68),
    (125, '2015-04-24 00:00:00', 79),
    (125, '2015-04-27 00:00:00', 55),
    (129, '2015-04-16 00:00:00', 12),
    (129, '2015-04-23 00:00:00', 39),
    (130, '2015-04-25 00:00:00', 76),
    (131, '2015-04-14 00:00:00', 23),
    (135, '2015-04-15 00:00:00', 120),
    (136, '2015-04-05 00:00:00', 83),
    (137, '2015-04-02 00:00:00', 69),
    (137, '2015-04-26 00:00:00', 53),
    (141, '2015-04-02 00:00:00', 116),
    (142, '2015-04-16 00:00:00', 46),
    (144, '2015-04-13 00:00:00', 98),
    (145, '2015-04-10 00:00:00', 77),
    (147, '2015-04-09 00:00:00', 73),
    (150, '2015-04-07 00:00:00', 72),
    (159, '2015-04-12 00:00:00', 131),
    (160, '2015-04-30 00:00:00', 74),
    (163, '2015-04-30 00:00:00', 77),
    (164, '2015-04-16 00:00:00', 43),
    (167, '2015-04-03 00:00:00', 56),
    (167, '2015-04-28 00:00:00', 32),
    (169, '2015-04-11 00:00:00', 34),
    (170, '2015-04-07 00:00:00', 119),
    (170, '2015-04-09 00:00:00', 85),
    (172, '2015-04-13 00:00:00', 57),
    (176, '2015-04-15 00:00:00', 52),
    (176, '2015-04-28 00:00:00', 56),
    (178, '2015-04-03 00:00:00', 14),
    (178, '2015-04-23 00:00:00', 79),
    (179, '2015-04-15 00:00:00', 82),
    (180, '2015-04-21 00:00:00', 65),
    (182, '2015-04-04 00:00:00', 54),
    (182, '2015-04-29 00:00:00', 21),
    (186, '2015-04-27 00:00:00', 67),
    (187, '2015-04-10 00:00:00', 45),
    (189, '2015-04-15 00:00:00', 47),
    (191, '2015-04-21 00:00:00', 33),
    (192, '2015-04-30 00:00:00', 96),
    (193, '2015-04-23 00:00:00', 45),
    (194, '2015-04-15 00:00:00', 72),
    (197, '2015-04-18 00:00:00', 58),
    (197, '2015-04-21 00:00:00', 54),
    (198, '2015-04-15 00:00:00', 94),
    (201, '2015-04-25 00:00:00', 60),
    (207, '2015-04-21 00:00:00', 74),
    (210, '2015-04-21 00:00:00', 45),
    (211, '2015-04-26 00:00:00', 41),
    (212, '2015-04-13 00:00:00', 76),
    (212, '2015-04-28 00:00:00', 65),
    (212, '2015-04-30 00:00:00', 32),
    (213, '2015-04-19 00:00:00', 42),
    (216, '2015-04-21 00:00:00', 12),
    (218, '2015-04-09 00:00:00', 65),
    (221, '2015-04-28 00:00:00', 109),
    (224, '2015-04-27 00:00:00', 67),
    (227, '2015-04-20 00:00:00', 61),
    (230, '2015-04-07 00:00:00', 51),
    (231, '2015-04-26 00:00:00', 61),
    (233, '2015-04-17 00:00:00', 68),
    (234, '2015-04-02 00:00:00', 58),
    (237, '2015-04-22 00:00:00', 68),
    (237, '2015-04-25 00:00:00', 59),
    (238, '2015-04-25 00:00:00', 61),
    (240, '2015-04-16 00:00:00', 30),
    (242, '2015-04-15 00:00:00', 54),
    (245, '2015-04-03 00:00:00', 55),
    (246, '2015-04-14 00:00:00', 106),
    (246, '2015-04-17 00:00:00', 61),
    (249, '2015-04-23 00:00:00', 80),
    (250, '2015-04-15 00:00:00', 59),
    (251, '2015-04-06 00:00:00', 39),
    (253, '2015-04-14 00:00:00', 44),
    (254, '2015-04-14 00:00:00', 57),
    (254, '2015-04-15 00:00:00', 74),
    (255, '2015-04-03 00:00:00', 81),
    (255, '2015-04-03 00:00:00', 49),
    (255, '2015-04-08 00:00:00', 16),
    (259, '2015-04-27 00:00:00', 46),
    (260, '2015-04-17 00:00:00', 56),
    (261, '2015-04-11 00:00:00', 46),
    (262, '2015-04-21 00:00:00', 87),
    (266, '2015-04-21 00:00:00', 94),
    (270, '2015-04-17 00:00:00', 66),
    (270, '2015-04-23 00:00:00', 54),
    (271, '2015-04-01 00:00:00', 55),
    (271, '2015-04-07 00:00:00', 57),
    (273, '2015-04-06 00:00:00', 70),
    (273, '2015-04-13 00:00:00', 87),
    (273, '2015-04-24 00:00:00', 43),
    (274, '2015-04-12 00:00:00', 50),
    (274, '2015-04-15 00:00:00', 34),
    (275, '2015-04-18 00:00:00', 34),
    (275, '2015-04-18 00:00:00', 100)
;

CREATE TABLE March
    ([user_id] int, [date] datetime, [transaction_amount] int)
;
    
INSERT INTO March
    ([user_id], [date], [transaction_amount])
VALUES
    (2, '2015-03-13 00:00:00', 67),
    (3, '2015-03-31 00:00:00', 26),
    (4, '2015-03-28 00:00:00', 63),
    (5, '2015-03-01 00:00:00', 45),
    (6, '2015-03-15 00:00:00', 32),
    (8, '2015-03-06 00:00:00', 66),
    (8, '2015-03-14 00:00:00', 73),
    (11, '2015-03-06 00:00:00', 91),
    (12, '2015-03-24 00:00:00', 36),
    (12, '2015-03-25 00:00:00', 14),
    (13, '2015-03-24 00:00:00', 22),
    (15, '2015-03-05 00:00:00', 113),
    (23, '2015-03-01 00:00:00', 19),
    (24, '2015-03-06 00:00:00', 34),
    (26, '2015-03-17 00:00:00', 53),
    (28, '2015-03-03 00:00:00', 33),
    (28, '2015-03-17 00:00:00', 77),
    (29, '2015-03-09 00:00:00', 37),
    (34, '2015-03-20 00:00:00', 73),
    (42, '2015-03-17 00:00:00', 88),
    (45, '2015-03-26 00:00:00', 112),
    (46, '2015-03-20 00:00:00', 36),
    (50, '2015-03-07 00:00:00', 73),
    (50, '2015-03-13 00:00:00', 41),
    (55, '2015-03-03 00:00:00', 62),
    (57, '2015-03-01 00:00:00', 51),
    (59, '2015-03-09 00:00:00', 59),
    (60, '2015-03-13 00:00:00', 26),
    (60, '2015-03-18 00:00:00', 15),
    (64, '2015-03-30 00:00:00', 20),
    (66, '2015-03-09 00:00:00', 26),
    (70, '2015-03-14 00:00:00', 81),
    (71, '2015-03-11 00:00:00', 10),
    (72, '2015-03-30 00:00:00', 45),
    (75, '2015-03-26 00:00:00', 77),
    (75, '2015-03-30 00:00:00', 71),
    (77, '2015-03-07 00:00:00', 63),
    (80, '2015-03-26 00:00:00', 63),
    (82, '2015-03-27 00:00:00', 77),
    (83, '2015-03-09 00:00:00', 13),
    (88, '2015-03-17 00:00:00', 64),
    (89, '2015-03-27 00:00:00', 62),
    (90, '2015-03-23 00:00:00', 81),
    (92, '2015-03-29 00:00:00', 51),
    (102, '2015-03-21 00:00:00', 65),
    (103, '2015-03-31 00:00:00', 39),
    (105, '2015-03-01 00:00:00', 48),
    (110, '2015-03-30 00:00:00', 72),
    (112, '2015-03-08 00:00:00', 31),
    (115, '2015-03-30 00:00:00', 13),
    (120, '2015-03-05 00:00:00', 29),
    (122, '2015-03-23 00:00:00', 72),
    (124, '2015-03-17 00:00:00', 48),
    (127, '2015-03-08 00:00:00', 22),
    (127, '2015-03-25 00:00:00', 59),
    (132, '2015-03-04 00:00:00', 38),
    (133, '2015-03-26 00:00:00', 69),
    (134, '2015-03-17 00:00:00', 55),
    (136, '2015-03-12 00:00:00', 75),
    (137, '2015-03-02 00:00:00', 71),
    (138, '2015-03-12 00:00:00', 54),
    (138, '2015-03-12 00:00:00', 29),
    (139, '2015-03-16 00:00:00', 19),
    (145, '2015-03-17 00:00:00', 53),
    (147, '2015-03-31 00:00:00', 18),
    (148, '2015-03-21 00:00:00', 32),
    (149, '2015-03-15 00:00:00', 52),
    (152, '2015-03-29 00:00:00', 130),
    (153, '2015-03-02 00:00:00', 45),
    (153, '2015-03-05 00:00:00', 93),
    (154, '2015-03-16 00:00:00', 34),
    (154, '2015-03-18 00:00:00', 36),
    (154, '2015-03-31 00:00:00', 39),
    (155, '2015-03-10 00:00:00', 106),
    (157, '2015-03-06 00:00:00', 94),
    (157, '2015-03-16 00:00:00', 60),
    (159, '2015-03-30 00:00:00', 70),
    (162, '2015-03-25 00:00:00', 90),
    (166, '2015-03-08 00:00:00', 34),
    (166, '2015-03-16 00:00:00', 11),
    (167, '2015-03-16 00:00:00', 115),
    (169, '2015-03-20 00:00:00', 58),
    (173, '2015-03-10 00:00:00', 70),
    (177, '2015-03-05 00:00:00', 37),
    (177, '2015-03-17 00:00:00', 53),
    (177, '2015-03-27 00:00:00', 50),
    (178, '2015-03-01 00:00:00', 45),
    (181, '2015-03-04 00:00:00', 22),
    (183, '2015-03-06 00:00:00', 113),
    (184, '2015-03-07 00:00:00', 40),
    (184, '2015-03-19 00:00:00', 17),
    (184, '2015-03-23 00:00:00', 45),
    (186, '2015-03-14 00:00:00', 92),
    (186, '2015-03-15 00:00:00', 29),
    (187, '2015-03-03 00:00:00', 58),
    (187, '2015-03-10 00:00:00', 74),
    (188, '2015-03-10 00:00:00', 94),
    (188, '2015-03-13 00:00:00', 42),
    (190, '2015-03-12 00:00:00', 33),
    (191, '2015-03-11 00:00:00', 61),
    (191, '2015-03-22 00:00:00', 43),
    (191, '2015-03-27 00:00:00', 49),
    (194, '2015-03-09 00:00:00', 24),
    (195, '2015-03-07 00:00:00', 44),
    (201, '2015-03-07 00:00:00', 10),
    (202, '2015-03-10 00:00:00', 70),
    (202, '2015-03-21 00:00:00', 76),
    (203, '2015-03-06 00:00:00', 57),
    (203, '2015-03-08 00:00:00', 46),
    (204, '2015-03-23 00:00:00', 29),
    (205, '2015-03-30 00:00:00', 52),
    (207, '2015-03-20 00:00:00', 47),
    (208, '2015-03-16 00:00:00', 57),
    (210, '2015-03-18 00:00:00', 13),
    (211, '2015-03-27 00:00:00', 104),
    (213, '2015-03-23 00:00:00', 38),
    (218, '2015-03-06 00:00:00', 41),
    (218, '2015-03-24 00:00:00', 74),
    (218, '2015-03-25 00:00:00', 13),
    (221, '2015-03-23 00:00:00', 58),
    (224, '2015-03-08 00:00:00', 77),
    (225, '2015-03-10 00:00:00', 90),
    (232, '2015-03-25 00:00:00', 74),
    (233, '2015-03-25 00:00:00', 56),
    (234, '2015-03-30 00:00:00', 42),
    (235, '2015-03-08 00:00:00', 42),
    (238, '2015-03-05 00:00:00', 29),
    (239, '2015-03-03 00:00:00', 11),
    (241, '2015-03-21 00:00:00', 68),
    (242, '2015-03-02 00:00:00', 34),
    (242, '2015-03-15 00:00:00', 92),
    (242, '2015-03-17 00:00:00', 17),
    (242, '2015-03-20 00:00:00', 35),
    (245, '2015-03-24 00:00:00', 121),
    (250, '2015-03-12 00:00:00', 57),
    (256, '2015-03-09 00:00:00', 108),
    (258, '2015-03-29 00:00:00', 77),
    (259, '2015-03-03 00:00:00', 27),
    (259, '2015-03-25 00:00:00', 78),
    (260, '2015-03-08 00:00:00', 80),
    (260, '2015-03-10 00:00:00', 78),
    (260, '2015-03-26 00:00:00', 103),
    (265, '2015-03-17 00:00:00', 44),
    (266, '2015-03-10 00:00:00', 83),
    (269, '2015-03-28 00:00:00', 31),
    (271, '2015-03-26 00:00:00', 41),
    (272, '2015-03-17 00:00:00', 39)
;

-- We have two tables. One table has all $ transactions from users during the month of March and one for the month of April.

-- Write a query that returns the total amount of money spent by each user. That is, the sum of the column transaction_amount for each user over both tables.
-- Write a query that returns day by day the cumulative sum of money spent by each user. 
-- That is, each day a user had a transcation, we should have how much money she has spent in total until that day. 
-- Obviously, the last day cumulative sum should match the numbers from the previous bullet point.
/*
SELECT CASE WHEN a.user_id IS NULL THEN b.user_id ELSE a.user_id END AS user_id,
ISNULL(a.total_trans,0)+ISNULL(b.total_trans,0) AS total_trans
FROM
(SELECT user_id, SUM(transaction_amount) AS total_trans
FROM March
GROUP BY user_id) AS a FULL OUTER JOIN
(SELECT user_id, SUM(transaction_amount) AS total_trans
 FROM April
 GROUp BY user_id) AS b ON a.user_id = b.user_id
*/

/*
-------- sol1 --------------
WITH t AS (
    SELECT *
    FROM March
    UNION ALL
    SELECT *
    FROM April)

SELECT b.user_id, b.date, SUM(a.transaction_amount) AS running_trans
FROM t AS a, t AS b
WHERE a.user_id = b.user_id AND a.date<=b.date
GROUP BY b.user_id, b.date
ORDER BY b.user_id, b.date

----------- sol2 ------------

SELECt *, SUM(transaction_amount) OVER(PARTITION BY user_id ORDER BY date) AS running_trans
FROM
(SELECT * FROM March UNION ALL SELECT * FROM April) AS t
ORDER BY user_id, date

-------------------------------------
-------------- Q5 ---------------------
-------------------------------------

CREATE TABLE users
    ([user_id] int, [sign_up_date] datetime)
;
    
INSERT INTO users
    ([user_id], [sign_up_date])
VALUES
    (121, '2015-01-02 00:00:00'),
    (617, '2015-01-02 00:00:00'),
    (619, '2015-01-02 00:00:00'),
    (625, '2015-01-02 00:00:00'),
    (753, '2015-01-02 00:00:00'),
    (1384, '2015-01-02 00:00:00'),
    (3364, '2015-01-02 00:00:00'),
    (4820, '2015-01-02 00:00:00'),
    (4853, '2015-01-02 00:00:00'),
    (5209, '2015-01-02 00:00:00'),
    (5354, '2015-01-02 00:00:00'),
    (6118, '2015-01-02 00:00:00'),
    (6564, '2015-01-02 00:00:00'),
    (6701, '2015-01-02 00:00:00'),
    (7302, '2015-01-02 00:00:00'),
    (7864, '2015-01-02 00:00:00'),
    (8116, '2015-01-02 00:00:00'),
    (8143, '2015-01-02 00:00:00'),
    (8608, '2015-01-02 00:00:00'),
    (8675, '2015-01-02 00:00:00'),
    (9230, '2015-01-02 00:00:00'),
    (9263, '2015-01-02 00:00:00'),
    (10113, '2015-01-02 00:00:00'),
    (10287, '2015-01-02 00:00:00'),
    (10370, '2015-01-02 00:00:00'),
    (12028, '2015-01-02 00:00:00'),
    (12597, '2015-01-03 00:00:00'),
    (12826, '2015-01-03 00:00:00'),
    (13014, '2015-01-03 00:00:00'),
    (13569, '2015-01-03 00:00:00'),
    (13616, '2015-01-03 00:00:00'),
    (15005, '2015-01-03 00:00:00'),
    (15338, '2015-01-03 00:00:00'),
    (15768, '2015-01-03 00:00:00'),
    (15822, '2015-01-03 00:00:00'),
    (15960, '2015-01-03 00:00:00'),
    (16627, '2015-01-03 00:00:00'),
    (17357, '2015-01-03 00:00:00'),
    (17576, '2015-01-03 00:00:00'),
    (17697, '2015-01-03 00:00:00'),
    (19030, '2015-01-03 00:00:00'),
    (21582, '2015-01-03 00:00:00'),
    (22376, '2015-01-04 00:00:00'),
    (22530, '2015-01-04 00:00:00'),
    (23027, '2015-01-04 00:00:00'),
    (23410, '2015-01-04 00:00:00'),
    (23424, '2015-01-04 00:00:00'),
    (24027, '2015-01-04 00:00:00'),
    (24069, '2015-01-04 00:00:00')
;

CREATE TABLE transactions
    ([user_id] int, [transaction_date] datetime, [transaction_amount] int)
;
    
INSERT INTO transactions
    ([user_id], [transaction_date], [transaction_amount])
VALUES
    (121, '2015-08-10 02:56:00', 43),
    (121, '2015-05-25 17:11:00', 47),
    (121, '2015-08-05 00:13:00', 44),
    (121, '2015-04-23 09:57:00', 34),
    (121, '2015-04-29 10:39:00', 45),
    (121, '2015-08-17 01:51:00', 45),
    (121, '2015-01-01 16:45:00', 94),
    (617, '2015-09-17 14:20:00', 30),
    (617, '2015-08-23 04:56:00', 49),
    (617, '2015-07-17 10:44:00', 58),
    (617, '2015-09-01 12:08:00', 56),
    (617, '2015-01-01 17:01:00', 66),
    (619, '2015-05-20 17:12:00', 57),
    (619, '2015-07-20 02:08:00', 66),
    (619, '2015-01-01 17:12:00', 56),
    (619, '2015-07-27 11:39:00', 67),
    (619, '2015-09-20 17:24:00', 50),
    (619, '2015-06-06 08:17:00', 58),
    (619, '2015-06-14 08:50:00', 41),
    (619, '2015-07-12 14:26:00', 47),
    (625, '2015-01-01 18:34:00', 51),
    (625, '2015-09-09 14:31:00', 57),
    (625, '2015-09-17 17:37:00', 45),
    (625, '2015-04-28 04:34:00', 52),
    (625, '2015-09-26 04:22:00', 38),
    (625, '2015-05-07 01:00:00', 65),
    (753, '2015-01-01 18:55:00', 60),
    (753, '2015-08-06 15:51:00', 46),
    (753, '2015-04-25 20:40:00', 65),
    (753, '2015-05-30 08:28:00', 32),
    (753, '2015-05-28 00:24:00', 51),
    (1384, '2015-08-10 08:12:00', 38),
    (1384, '2015-01-01 19:08:00', 67),
    (1384, '2015-07-31 10:56:00', 40),
    (3364, '2015-07-25 12:40:00', 38),
    (3364, '2015-09-09 00:23:00', 33),
    (3364, '2015-05-09 12:15:00', 45),
    (3364, '2015-09-08 16:09:00', 37),
    (3364, '2015-05-24 11:26:00', 62),
    (3364, '2015-01-01 20:18:00', 69),
    (3364, '2015-04-29 20:19:00', 52),
    (4820, '2015-01-01 20:22:00', 61),
    (4820, '2015-08-27 06:31:00', 55),
    (4820, '2015-07-06 18:44:00', 39),
    (4820, '2015-07-18 07:46:00', 37),
    (4820, '2015-09-10 04:54:00', 32),
    (4853, '2015-06-03 06:08:00', 46),
    (4853, '2015-07-21 13:26:00', 33),
    (4853, '2015-04-02 06:40:00', 44),
    (4853, '2015-01-01 20:45:00', 56),
    (5209, '2015-05-03 07:42:00', 60),
    (5209, '2015-06-03 07:40:00', 38),
    (5209, '2015-04-08 14:13:00', 52),
    (5209, '2015-09-16 11:39:00', 38),
    (5209, '2015-07-25 14:25:00', 25),
    (5209, '2015-04-30 23:14:00', 55),
    (5209, '2015-06-05 02:42:00', 58),
    (5209, '2015-08-16 02:27:00', 56),
    (5209, '2015-08-29 06:39:00', 43),
    (5209, '2015-09-29 12:26:00', 59),
    (5209, '2015-01-01 21:18:00', 57),
    (5354, '2015-09-05 06:01:00', 61),
    (5354, '2015-01-01 21:35:00', 53),
    (5354, '2015-07-27 11:07:00', 65),
    (6118, '2015-04-19 09:55:00', 45),
    (6118, '2015-01-01 21:53:00', 58),
    (6118, '2015-07-08 19:17:00', 39),
    (6118, '2015-09-20 17:21:00', 61),
    (6564, '2015-08-01 15:06:00', 48),
    (6564, '2015-01-01 22:53:00', 47),
    (6564, '2015-08-26 12:33:00', 42),
    (6564, '2015-08-05 20:46:00', 68),
    (6564, '2015-09-11 07:57:00', 44),
    (6564, '2015-06-21 09:06:00', 62),
    (6564, '2015-05-22 05:08:00', 46),
    (6701, '2015-09-22 07:26:00', 47),
    (6701, '2015-05-02 15:37:00', 56),
    (6701, '2015-01-02 00:18:00', 77),
    (6701, '2015-09-05 05:35:00', 41),
    (6701, '2015-07-28 02:49:00', 69),
    (6701, '2015-05-06 15:58:00', 45),
    (6701, '2015-09-18 18:10:00', 55),
    (7302, '2015-09-16 17:30:00', 53),
    (7302, '2015-01-02 00:52:00', 61),
    (7302, '2015-05-22 21:29:00', 45),
    (7302, '2015-04-03 23:14:00', 58),
    (7302, '2015-06-23 23:02:00', 47),
    (7302, '2015-09-05 10:08:00', 72),
    (7302, '2015-08-14 14:30:00', 45),
    (7302, '2015-08-10 18:56:00', 45),
    (7302, '2015-09-28 15:02:00', 55),
    (7864, '2015-05-18 03:24:00', 59),
    (7864, '2015-07-04 11:08:00', 43),
    (7864, '2015-04-08 02:46:00', 56),
    (7864, '2015-01-02 05:07:00', 63),
    (7864, '2015-09-04 03:31:00', 51),
    (7864, '2015-09-01 12:02:00', 60),
    (8116, '2015-06-07 08:03:00', 69),
    (8116, '2015-06-10 08:43:00', 63),
    (8116, '2015-01-02 06:01:00', 65),
    (8116, '2015-09-22 21:50:00', 63),
    (8116, '2015-08-04 07:05:00', 45),
    (8143, '2015-06-23 00:14:00', 50),
    (8143, '2015-04-08 12:03:00', 56),
    (8143, '2015-04-07 02:34:00', 42),
    (8143, '2015-05-18 15:32:00', 42),
    (8143, '2015-08-09 03:26:00', 57),
    (8143, '2015-01-02 06:51:00', 54),
    (8143, '2015-08-18 14:52:00', 63),
    (8143, '2015-05-11 00:19:00', 45),
    (8608, '2015-07-06 03:07:00', 71),
    (8608, '2015-09-02 13:52:00', 54),
    (8608, '2015-07-23 12:23:00', 56),
    (8608, '2015-01-02 07:52:00', 31),
    (8608, '2015-05-18 00:05:00', 41),
    (8608, '2015-07-22 12:17:00', 53),
    (8675, '2015-05-01 00:29:00', 39),
    (8675, '2015-08-08 02:51:00', 66),
    (8675, '2015-06-17 16:55:00', 32),
    (8675, '2015-08-15 20:45:00', 53),
    (8675, '2015-01-02 10:35:00', 60),
    (9230, '2015-01-02 10:38:00', 47),
    (9230, '2015-08-11 07:50:00', 55),
    (9263, '2015-04-01 18:35:00', 76),
    (9263, '2015-09-11 08:43:00', 54),
    (9263, '2015-09-13 06:53:00', 61),
    (9263, '2015-07-22 06:29:00', 28),
    (9263, '2015-06-08 07:37:00', 26),
    (9263, '2015-07-10 05:07:00', 36),
    (9263, '2015-01-02 10:43:00', 38),
    (9263, '2015-04-25 08:28:00', 59),
    (10113, '2015-08-16 17:30:00', 47),
    (10113, '2015-01-02 10:49:00', 53),
    (10113, '2015-04-03 06:58:00', 63),
    (10113, '2015-06-20 15:22:00', 63),
    (10113, '2015-07-21 08:13:00', 49),
    (10113, '2015-04-10 12:56:00', 49),
    (10287, '2015-06-24 06:19:00', 49),
    (10287, '2015-09-25 13:59:00', 26),
    (10287, '2015-08-13 02:31:00', 49),
    (10287, '2015-01-02 13:28:00', 42),
    (10370, '2015-05-18 15:58:00', 36),
    (10370, '2015-01-02 14:55:00', 31),
    (10370, '2015-05-30 06:10:00', 53),
    (12028, '2015-01-02 15:49:00', 51),
    (12028, '2015-05-22 22:33:00', 48),
    (12028, '2015-04-21 13:21:00', 37),
    (12028, '2015-09-26 04:43:00', 54),
    (12028, '2015-08-19 17:39:00', 47),
    (12028, '2015-04-08 17:36:00', 56),
    (12028, '2015-05-08 10:40:00', 45),
    (12028, '2015-05-20 01:53:00', 69),
    (12028, '2015-07-19 21:14:00', 51),
    (12597, '2015-09-16 21:54:00', 48),
    (12597, '2015-08-04 09:47:00', 51),
    (12597, '2015-01-02 16:46:00', 62),
    (12826, '2015-01-02 19:40:00', 42),
    (12826, '2015-06-21 19:28:00', 46),
    (12826, '2015-06-14 03:00:00', 60),
    (12826, '2015-07-18 23:27:00', 49),
    (12826, '2015-07-07 09:53:00', 57),
    (12826, '2015-06-04 20:20:00', 63),
    (12826, '2015-07-18 06:33:00', 48),
    (13014, '2015-08-07 17:16:00', 61),
    (13014, '2015-08-19 11:58:00', 63),
    (13014, '2015-01-02 19:43:00', 45),
    (13569, '2015-06-22 14:26:00', 23),
    (13569, '2015-06-11 02:11:00', 53),
    (13569, '2015-06-01 21:03:00', 42),
    (13569, '2015-06-03 15:21:00', 39),
    (13569, '2015-07-13 11:37:00', 67),
    (13569, '2015-01-02 20:58:00', 65),
    (13616, '2015-05-24 02:53:00', 46),
    (13616, '2015-01-02 21:07:00', 69),
    (13616, '2015-04-19 04:17:00', 60),
    (13616, '2015-06-02 14:52:00', 33),
    (13616, '2015-05-23 07:49:00', 57),
    (13616, '2015-04-06 13:43:00', 45),
    (15005, '2015-05-24 12:34:00', 63),
    (15005, '2015-01-02 22:29:00', 55),
    (15005, '2015-07-25 07:49:00', 33),
    (15005, '2015-05-04 05:41:00', 65),
    (15338, '2015-04-22 21:11:00', 36),
    (15338, '2015-04-20 18:11:00', 47),
    (15338, '2015-04-20 23:48:00', 56),
    (15338, '2015-08-21 06:31:00', 85),
    (15338, '2015-01-03 01:42:00', 48),
    (15338, '2015-06-14 15:31:00', 67),
    (15338, '2015-08-28 04:13:00', 71),
    (15338, '2015-04-22 14:20:00', 60),
    (15338, '2015-07-10 04:49:00', 46),
    (15768, '2015-01-03 01:57:00', 53),
    (15768, '2015-07-30 04:38:00', 47),
    (15768, '2015-04-14 14:08:00', 63),
    (15768, '2015-06-21 05:45:00', 31),
    (15768, '2015-09-14 04:28:00', 48),
    (15768, '2015-07-19 23:12:00', 50),
    (15768, '2015-07-18 01:59:00', 39),
    (15768, '2015-08-27 23:01:00', 50),
    (15822, '2015-01-03 04:35:00', 72),
    (15822, '2015-08-19 05:28:00', 64),
    (15822, '2015-05-04 07:17:00', 77),
    (15822, '2015-04-17 10:32:00', 29),
    (15822, '2015-05-23 06:07:00', 49),
    (15960, '2015-05-21 15:31:00', 71),
    (15960, '2015-01-03 04:58:00', 68),
    (15960, '2015-06-17 05:48:00', 45),
    (15960, '2015-04-07 23:16:00', 58),
    (16627, '2015-01-03 06:36:00', 74),
    (16627, '2015-07-28 04:36:00', 55),
    (16627, '2015-04-03 22:17:00', 54),
    (16627, '2015-04-27 14:58:00', 58),
    (16627, '2015-05-20 05:11:00', 44),
    (16627, '2015-06-03 15:58:00', 52),
    (16627, '2015-08-21 10:54:00', 63),
    (16627, '2015-08-03 18:37:00', 38),
    (17357, '2015-06-08 01:38:00', 60),
    (17357, '2015-01-03 07:40:00', 36),
    (17357, '2015-07-31 15:38:00', 32),
    (17357, '2015-08-30 01:44:00', 47),
    (17357, '2015-05-04 12:13:00', 59),
    (17357, '2015-06-08 10:37:00', 50),
    (17357, '2015-05-17 17:15:00', 45),
    (17357, '2015-09-09 12:31:00', 40),
    (17357, '2015-05-02 16:23:00', 48),
    (17357, '2015-04-14 13:51:00', 49),
    (17576, '2015-09-25 01:13:00', 34),
    (17576, '2015-01-03 09:10:00', 42),
    (17576, '2015-08-14 14:05:00', 48),
    (17576, '2015-09-19 16:40:00', 54),
    (17697, '2015-01-03 09:53:00', 22),
    (17697, '2015-08-03 11:10:00', 57),
    (17697, '2015-05-10 02:32:00', 35),
    (17697, '2015-07-01 20:12:00', 52),
    (17697, '2015-04-30 16:02:00', 32),
    (17697, '2015-09-25 23:41:00', 44),
    (19030, '2015-01-03 13:53:00', 55),
    (19030, '2015-07-12 20:29:00', 42),
    (19030, '2015-09-15 07:12:00', 56),
    (19030, '2015-07-15 03:07:00', 55),
    (19030, '2015-08-18 18:46:00', 46),
    (19030, '2015-05-07 11:59:00', 44),
    (19030, '2015-08-21 21:48:00', 36),
    (21582, '2015-01-03 14:16:00', 49),
    (21582, '2015-08-14 11:23:00', 52),
    (21582, '2015-08-19 16:55:00', 64),
    (21582, '2015-04-26 20:48:00', 46),
    (22376, '2015-09-24 00:47:00', 71),
    (22376, '2015-05-01 09:48:00', 51),
    (22376, '2015-09-11 08:12:00', 50),
    (22376, '2015-01-03 16:29:00', 65),
    (22376, '2015-08-02 14:13:00', 50),
    (22530, '2015-05-15 04:21:00', 33),
    (22530, '2015-07-12 10:16:00', 49),
    (22530, '2015-08-11 04:36:00', 50),
    (22530, '2015-06-30 07:14:00', 28),
    (22530, '2015-09-20 21:09:00', 58),
    (22530, '2015-06-13 06:58:00', 52),
    (22530, '2015-08-14 00:30:00', 41),
    (22530, '2015-06-22 21:13:00', 64),
    (22530, '2015-01-03 17:20:00', 39),
    (23027, '2015-04-16 20:00:00', 66),
    (23027, '2015-08-25 06:59:00', 32),
    (23027, '2015-09-06 23:32:00', 37),
    (23027, '2015-08-10 21:23:00', 52),
    (23027, '2015-09-09 02:11:00', 45),
    (23027, '2015-01-03 18:31:00', 55),
    (23410, '2015-09-17 14:33:00', 53),
    (23410, '2015-06-16 02:40:00', 50),
    (23410, '2015-05-14 15:28:00', 56),
    (23410, '2015-05-02 20:21:00', 60),
    (23410, '2015-09-14 16:50:00', 44),
    (23410, '2015-01-03 20:20:00', 56),
    (23410, '2015-06-20 09:54:00', 53),
    (23424, '2015-01-03 20:34:00', 47),
    (23424, '2015-06-25 00:39:00', 36),
    (23424, '2015-06-28 11:59:00', 37),
    (23424, '2015-07-06 15:14:00', 48),
    (23424, '2015-04-08 17:38:00', 45),
    (23424, '2015-06-05 05:45:00', 60),
    (23424, '2015-09-07 11:35:00', 79),
    (24027, '2015-08-04 14:32:00', 50),
    (24027, '2015-05-05 06:46:00', 40),
    (24027, '2015-08-25 16:19:00', 50),
    (24027, '2015-01-03 20:47:00', 60),
    (24069, '2015-05-17 04:21:00', 42),
    (24069, '2015-05-24 05:17:00', 50),
    (24069, '2015-04-04 13:58:00', 45),
    (24069, '2015-01-03 21:48:00', 46),
    (24069, '2015-07-02 17:53:00', 63),
    (24069, '2015-07-09 03:02:00', 47)
;

-- We have two tables. One is user id and their signup date. The other one shows all transactions done by those users, 
-- when the transaction happens and its corresponding dollar amount.

-- Find the average and median transaction amount only considering those transactions that happen on the same date as that user signed-up.

--------------- sol1 ----------------
/*
SELECT AVG(tot_trans) AS avg_trans, 
AVG(median_trans) AS median_trans
FROM (
SELECT a.user_id, ISNULL(tot_trans,0) AS tot_trans, PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY ISNULL(tot_trans,0)) OVER() AS median_trans
FROM users AS a LEFT JOIN 
(SELECT user_id, transaction_date, SUM(transaction_amount) AS tot_trans
 FROM transactions 
 GROUP BY user_id, transaction_date) AS b 
ON a.user_id = b.user_id AND a.sign_up_date = CONVERT(date, b.transaction_date)
) AS t

*/
--------------- sol2 ----------------
WITH t AS (
SELECT a.user_id, ISNULL(tot_trans,0) AS tot_trans, ROW_NUMBER() OVER(ORDER BY ISNULL(tot_trans,0)) AS rank_trans
FROM users AS a LEFT JOIN 
(SELECT user_id, transaction_date, SUM(transaction_amount) AS tot_trans
 FROM transactions 
 GROUP BY user_id, transaction_date) AS b 
ON a.user_id = b.user_id AND a.sign_up_date = CONVERT(date, b.transaction_date)
),
rn AS (SELECT (COUNT(*)+1)/2 AS k
       FROM t
       UNION ALL
       SELECT (COUNT(*)+2)/2 --- directly takes floor
       FROM t)

SELECT *
FROM
(SELECT tot_trans AS median_trans
 FROM t
 WHERE rank_trans IN (SELECT * FROM rn)) a CROSS JOIN
(SELECT AVG(tot_trans) AS avg_trans FROM t) b


run \| edit \| history \| help	0