LeetCode SQL - Analysis

Product Sales Analysis

1068. Product Sales Analysis I

1
2
3
4
5
6
7
SELECT
product_name,
`year`,
price
FROM
Sales
LEFT JOIN Product USING (product_id);

1069. Product Sales Analysis II

1
2
3
4
5
6
7
SELECT
product_id,
SUM(quantity) AS total_quantity
FROM
Sales
GROUP BY
1;

1070. Product Sales Analysis III

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
WITH
cte AS (
SELECT
product_id,
`year`,
RANK() OVER (
PARTITION BY
product_id
ORDER BY
`year` asc
) AS year_r,
quantity,
price
FROM
sales
)
SELECT
product_id,
`year` AS first_year,
quantity,
price
FROM
cte
WHERE
year_r = 1;

2324. Product Sales Analysis IV

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
WITH
cte AS (
SELECT
user_id,
product_id,
DENSE_RANK() OVER (
PARTITION BY
user_id
ORDER BY
SUM(price * quantity) desc
) AS amount_dr
FROM
Sales
INNER JOIN Product USING (product_id)
GROUP BY
1,
2
)
SELECT
user_id,
product_id
FROM
cte
WHERE
amount_dr = 1;

2329. Product Sales Analysis V

1
2
3
4
5
6
7
8
9
10
11
SELECT
user_id,
SUM(quantity * price) AS spending
FROM
Sales
INNER JOIN Product USING (product_id)
GROUP BY
1
ORDER BY
2 desc,
1 asc;

Market Analysis

1158. Market Analysis I

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
DROP TABLE IF EXISTS Users;
DROP TABLE IF EXISTS Orders;
DROP TABLE IF EXISTS Items;
CREATE TABLE
If NOT EXISTS Users (user_id INT, join_date DATE, favorite_brand VARCHAR(10));
CREATE TABLE
If NOT EXISTS Orders (order_id INT, order_date DATE, item_id INT, buyer_id INT, seller_id INT);
CREATE TABLE
If NOT EXISTS Items (item_id INT, item_brand VARCHAR(10));
TRUNCATE TABLE Users;
INSERT INTO
Users (user_id, join_date, favorite_brand)
VALUES
('1', '2018-01-01', 'Lenovo');
INSERT INTO
Users (user_id, join_date, favorite_brand)
VALUES
('2', '2018-02-09', 'Samsung');
INSERT INTO
Users (user_id, join_date, favorite_brand)
VALUES
('3', '2018-01-19', 'LG');
INSERT INTO
Users (user_id, join_date, favorite_brand)
VALUES
('4', '2018-05-21', 'HP');
TRUNCATE TABLE Orders;
INSERT INTO
Orders (order_id, order_date, item_id, buyer_id, seller_id)
VALUES
('1', '2019-08-01', '4', '1', '2');
INSERT INTO
Orders (order_id, order_date, item_id, buyer_id, seller_id)
VALUES
('2', '2018-08-02', '2', '1', '3');
INSERT INTO
Orders (order_id, order_date, item_id, buyer_id, seller_id)
VALUES
('3', '2019-08-03', '3', '2', '3');
INSERT INTO
Orders (order_id, order_date, item_id, buyer_id, seller_id)
VALUES
('4', '2018-08-04', '1', '4', '2');
INSERT INTO
Orders (order_id, order_date, item_id, buyer_id, seller_id)
VALUES
('5', '2018-08-04', '1', '3', '4');
INSERT INTO
Orders (order_id, order_date, item_id, buyer_id, seller_id)
VALUES
('6', '2019-08-05', '2', '2', '4');
TRUNCATE TABLE Items;
INSERT INTO
Items (item_id, item_brand)
VALUES
('1', 'Samsung');
INSERT INTO
Items (item_id, item_brand)
VALUES
('2', 'Lenovo');
INSERT INTO
Items (item_id, item_brand)
VALUES
('3', 'LG');
INSERT INTO
Items (item_id, item_brand)
VALUES
('4', 'HP');
1
2
3
4
5
6
7
8
9
10
11
SELECT
u.user_id AS buyer_id,
join_date,
COUNT(order_id) AS orders_in_2019
FROM
Users u
LEFT JOIN Orders o ON u.user_id = o.buyer_id
AND YEAR (order_date) = 2019
GROUP BY
1,
2

Notice we cannot use where year(date) = 2019 to filter order made in 2019.

1
2
3
4
5
6
7
8
9
10
11
12
SELECT
u.user_id AS buyer_id,
join_date,
ifnull (COUNT(order_id), 0) AS orders_in_2019
FROM
Users u
LEFT JOIN Orders o ON u.user_id = o.buyer_id
WHERE
YEAR (order_date) = 2019
GROUP BY
1,
2;

Because where cannot get the null records.


1159. Market Analysis II

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
DROP TABLE IF EXISTS Users;
DROP TABLE IF EXISTS Orders;
DROP TABLE IF EXISTS Items;
CREATE TABLE
If NOT EXISTS Users (user_id INT, join_date DATE, favorite_brand VARCHAR(10));
CREATE TABLE
If NOT EXISTS Orders (order_id INT, order_date DATE, item_id INT, buyer_id INT, seller_id INT);
CREATE TABLE
If NOT EXISTS Items (item_id INT, item_brand VARCHAR(10));
TRUNCATE TABLE Users;
INSERT INTO
Users (user_id, join_date, favorite_brand)
VALUES
('1', '2019-01-01', 'Lenovo');
INSERT INTO
Users (user_id, join_date, favorite_brand)
VALUES
('2', '2019-02-09', 'Samsung');
INSERT INTO
Users (user_id, join_date, favorite_brand)
VALUES
('3', '2019-01-19', 'LG');
INSERT INTO
Users (user_id, join_date, favorite_brand)
VALUES
('4', '2019-05-21', 'HP');
TRUNCATE TABLE Orders;
INSERT INTO
Orders (order_id, order_date, item_id, buyer_id, seller_id)
VALUES
('1', '2019-08-01', '4', '1', '2');
INSERT INTO
Orders (order_id, order_date, item_id, buyer_id, seller_id)
VALUES
('2', '2019-08-02', '2', '1', '3');
INSERT INTO
Orders (order_id, order_date, item_id, buyer_id, seller_id)
VALUES
('3', '2019-08-03', '3', '2', '3');
INSERT INTO
Orders (order_id, order_date, item_id, buyer_id, seller_id)
VALUES
('4', '2019-08-04', '1', '4', '2');
INSERT INTO
Orders (order_id, order_date, item_id, buyer_id, seller_id)
VALUES
('5', '2019-08-04', '1', '3', '4');
INSERT INTO
Orders (order_id, order_date, item_id, buyer_id, seller_id)
VALUES
('6', '2019-08-05', '2', '2', '4');
TRUNCATE TABLE Items;
INSERT INTO
Items (item_id, item_brand)
VALUES
('1', 'Samsung');
INSERT INTO
Items (item_id, item_brand)
VALUES
('2', 'Lenovo');
INSERT INTO
Items (item_id, item_brand)
VALUES
('3', 'LG');
INSERT INTO
Items (item_id, item_brand)
VALUES
('4', 'HP');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
WITH
cte AS (
SELECT
seller_id,
item_id,
RANK() OVER (
PARTITION BY
seller_id
ORDER BY
order_date asc
) AS order_dr
FROM
Orders
)
SELECT
user_id AS seller_id,
if (item_brand = favorite_brand, 'yes', 'no') AS second_item_fav_brand
FROM
Users u
LEFT JOIN cte c ON u.user_id = seller_id
AND order_dr = 2
LEFT JOIN Items USING (item_id);

Sales Analysis

1082. Sales Analysis I

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
WITH
cte AS (
SELECT
seller_id,
DENSE_RANK() OVER (
ORDER BY
SUM(price) desc
) AS sales_dr
FROM
Sales
GROUP BY
seller_id
)
SELECT
seller_id
FROM
cte
WHERE
sales_dr = 1;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
WITH
cte AS (
SELECT
seller_id,
SUM(price) AS price
FROM
sales
GROUP BY
seller_id
)
SELECT
seller_id
FROM
cte
WHERE
price = (
SELECT
MAX(price)
FROM
cte
);

1083. Sales Analysis II

1
2
3
4
5
6
7
8
9
10
SELECT
buyer_id
FROM
Sales
INNER JOIN Product USING (product_id)
GROUP BY
1
HAVING
SUM(product_name = 'S8') > 0
AND SUM(product_name = 'iPhone') = 0;

1084. Sales Analysis III

1
2
3
4
5
6
7
8
9
10
11
12
SELECT
product_id,
product_name
FROM
Sales
INNER JOIN Product USING (product_id)
GROUP BY
1,
2
HAVING
MIN(sale_date) >= '2019-01-01'
AND MAX(sale_date) <= '2019-03-31';

The following is wrong since a sale_date may < '2019-01-01' or > '2019-03-31'.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT
product_id,
product_name
FROM
Sales
INNER JOIN Product USING (product_id)
WHERE
YEAR (sale_date) = 2019
AND quarter (sale_date) = 1
AND product_id NOT IN (
SELECT
product_id
FROM
Sales
WHERE
YEAR (sale_date) = 2019
AND quarter (sale_date) != 1
);

Game Play Analysis

511. Game Play Analysis I

1
2
3
4
5
6
7
SELECT
player_id,
MIN(event_date) AS first_login
FROM
Activity
GROUP BY
1;

512. Game Play Analysis II

Universal solution

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
WITH
cte AS (
SELECT
player_id,
device_id,
event_date,
DENSE_RANK() OVER (
PARTITION BY
player_id
ORDER BY
event_date asc
) AS event_r
FROM
Activity
)
SELECT
player_id,
device_id
FROM
cte
WHERE
event_r = 1;

FIRST_VALUE needs to be used with DISTINCT

1
2
3
4
5
6
7
8
9
10
SELECT DISTINCT
player_id,
FIRST_VALUE (device_id) OVER (
PARTITION BY
player_id
ORDER BY
event_date asc
) AS device_id
FROM
Activity;

534. Game Play Analysis III

1
2
3
4
5
6
7
8
9
10
11
SELECT
player_id,
event_date,
SUM(games_played) OVER (
PARTITION BY
player_id
ORDER BY
event_date asc
) AS games_played_so_far
FROM
Activity;

550. Game Play Analysis IV

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
DROP TABLE if EXISTS Activity;
CREATE TABLE
If NOT EXISTS Activity (player_id INT, device_id INT, event_date DATE, games_played INT);
TRUNCATE TABLE Activity;
INSERT INTO
Activity (player_id, device_id, event_date, games_played)
VALUES
('1', '2', '2016-03-01', '5');
INSERT INTO
Activity (player_id, device_id, event_date, games_played)
VALUES
('1', '2', '2016-03-02', '6');
INSERT INTO
Activity (player_id, device_id, event_date, games_played)
VALUES
('2', '3', '2017-06-25', '1');
INSERT INTO
Activity (player_id, device_id, event_date, games_played)
VALUES
('3', '1', '2016-03-02', '0');
INSERT INTO
Activity (player_id, device_id, event_date, games_played)
VALUES
('3', '4', '2018-07-03', '5');

Hard Code solution

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- O(nlogn), O(n)
WITH
day1 AS (
SELECT
player_id,
MIN(event_date) AS event_date
FROM
Activity
GROUP BY
1
),
day2 AS (
SELECT
day1.player_id,
day2.event_date
FROM
day1
LEFT JOIN Activity AS day2 ON day1.player_id = day2.player_id
-- Missing day2.event_date will be null, then we can make the fraction later
AND day1.event_date + INTERVAL 1 DAY = day2.event_date
)
SELECT
ROUND(COUNT(event_date) / COUNT(player_id), 2) AS fraction
FROM
day2;

Universal Solution 1:

  1. Identify the first login date for each player.
  2. Generates all expected consecutive login dates starting from the first login date for each player.
  3. Checks if the expected consecutive login dates are present in the Activity table.
  4. Counts the number of consecutive login days for each player and filters those with at least N consecutive days.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
-- O(nlogn), O(n)
WITH RECURSIVE
Date_Series AS (
SELECT
0 AS day_increment
UNION ALL
SELECT
day_increment + 1
FROM
Date_Series
WHERE
day_increment < 1 -- variable
),
day1 AS (
SELECT
player_id,
MIN(event_date) AS event_date
FROM
Activity
GROUP BY
player_id
),
all_days AS (
SELECT
player_id,
-- event_date + interval ds.day_increment DAY
date_add (event_date, INTERVAL day_increment DAY) AS event_date
FROM
day1
INNER JOIN Date_Series ds
),
-- SELECT al.player_id,
-- al.event_date AS all_days,
-- a.event_date
-- FROM all_days al
-- LEFT JOIN Activity a ON a.player_id = al.player_id
-- AND a.event_date = al.event_date
-- ORDER BY 1 ASC,
-- all_days ASC
cte AS (
SELECT
a.player_id
FROM
all_days al
LEFT JOIN Activity a ON a.player_id = al.player_id
AND a.event_date = al.event_date
GROUP BY
a.player_id
HAVING
COUNT(a.event_date) >= 2 -- variable
)
SELECT
ROUND(
COUNT(player_id) / (
SELECT
COUNT(*)
FROM
day1
),
2
) AS fraction
FROM
cte

Universal Solution 2:

  1. Identify the first login date for each player.
  2. Generate row numbers to help identify consecutive dates.
  3. Calculate the number of consecutive logins starting from the first login date.
  4. Determine the fraction of players who have at least N consecutive logins starting from their first login date.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
-- O(nlogn), O(n)
WITH FirstLogin AS (
SELECT
player_id,
MIN(event_date) AS first_login_date
FROM
Activity
GROUP BY
player_id
),
PlayerLogins AS (
SELECT
player_id,
event_date,
ROW_NUMBER() OVER (PARTITION BY player_id ORDER BY event_date) AS row_num
FROM
Activity
),
ConsecutiveLogins AS (
SELECT
player_id,
event_date,
row_num,
DATE_SUB(event_date, INTERVAL row_num DAY) AS event_group
FROM
PlayerLogins
),
-- For question of max consecutive logins, this cte can be skipped
EventGroups AS (
SELECT
player_id,
event_date,
row_num,
event_group,
RANK() OVER(PARTITION BY player_id ORDER BY event_group) as event_group_rank
FROM
ConsecutiveLogins
),
LoginStreaks AS (
SELECT
player_id,
COUNT(*) AS streak_length
FROM
EventGroups
WHERE
event_group_rank = 1 -- Filter the event_group starting from the 1st login date
GROUP BY
player_id, event_group
),
MaxStreaks AS (
SELECT
player_id,
MAX(streak_length) AS max_streak
FROM
LoginStreaks
GROUP BY
player_id
)
SELECT
ROUND(COUNT(CASE WHEN max_streak >= 2 THEN 1 END) / COUNT(*), 2) AS fraction
FROM
MaxStreaks;

1097. Game Play Analysis V

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
DROP TABLE if EXISTS Activity;
CREATE TABLE
If NOT EXISTS Activity (player_id INT, device_id INT, event_date DATE, games_played INT);
TRUNCATE TABLE Activity;
INSERT INTO
Activity (player_id, device_id, event_date, games_played)
VALUES
('1', '2', '2016-03-01', '5');
INSERT INTO
Activity (player_id, device_id, event_date, games_played)
VALUES
('1', '2', '2016-03-02', '6');
INSERT INTO
Activity (player_id, device_id, event_date, games_played)
VALUES
('2', '3', '2017-06-25', '1');
INSERT INTO
Activity (player_id, device_id, event_date, games_played)
VALUES
('3', '1', '2016-03-01', '0');
INSERT INTO
Activity (player_id, device_id, event_date, games_played)
VALUES
('3', '4', '2018-07-03', '5');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
WITH
day1 AS (
SELECT
player_id,
MIN(event_date) AS event_date
FROM
Activity
GROUP BY
1
)
SELECT
day1.event_date AS install_dt,
COUNT(day1.player_id) AS installs,
ROUND(COUNT(day2.player_id) / COUNT(day1.player_id), 2) AS Day1_retention
FROM
day1
LEFT JOIN Activity day2 ON day2.player_id = day1.player_id
AND day2.event_date = day1.event_date + INTERVAL 1 DAY
GROUP BY
1;

One more thing. What if we want to get the longest consecutive logging records per user from his first logging date?

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
WITH RECURSIVE
Date_Series AS (
SELECT
0 AS day_increment
UNION ALL
SELECT
day_increment + 1
FROM
Date_Series
WHERE
day_increment < 7
),
day1 AS (
SELECT
player_id,
MIN(event_date) AS event_date
FROM
Activity
GROUP BY
player_id
),
all_days AS (
SELECT
player_id,
-- event_date + interval ds.day_increment DAY
date_add (event_date, INTERVAL day_increment DAY) AS event_date
FROM
day1
INNER JOIN Date_Series ds
)
SELECT
al.player_id,
al.event_date AS all_dayes,
a.event_date
FROM
all_days al
LEFT JOIN Activity a ON a.player_id = al.player_id
AND a.event_date = al.event_date
ORDER BY
1 ASC,
all_dayes ASC

Food Delivery

1174. Immediate Food Delivery II

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
DROP TABLE if EXISTS Delivery;
CREATE TABLE
If NOT EXISTS Delivery (
delivery_id INT,
customer_id INT,
order_date DATE,
customer_pref_delivery_date DATE
);
TRUNCATE TABLE Delivery;
INSERT INTO
Delivery (delivery_id, customer_id, order_date, customer_pref_delivery_date)
VALUES
('1', '1', '2019-08-01', '2019-08-02');
INSERT INTO
Delivery (delivery_id, customer_id, order_date, customer_pref_delivery_date)
VALUES
('2', '2', '2019-08-02', '2019-08-02');
INSERT INTO
Delivery (delivery_id, customer_id, order_date, customer_pref_delivery_date)
VALUES
('3', '1', '2019-08-11', '2019-08-12');
INSERT INTO
Delivery (delivery_id, customer_id, order_date, customer_pref_delivery_date)
VALUES
('4', '3', '2019-08-24', '2019-08-24');
INSERT INTO
Delivery (delivery_id, customer_id, order_date, customer_pref_delivery_date)
VALUES
('5', '3', '2019-08-21', '2019-08-22');
INSERT INTO
Delivery (delivery_id, customer_id, order_date, customer_pref_delivery_date)
VALUES
('6', '2', '2019-08-11', '2019-08-13');
INSERT INTO
Delivery (delivery_id, customer_id, order_date, customer_pref_delivery_date)
VALUES
('7', '4', '2019-08-09', '2019-08-09');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
WITH
cte AS (
SELECT
*,
if (order_date = customer_pref_delivery_date, "immediate", "schedule") AS order_type
FROM
Delivery
)
SELECT
*
FROM
cte;
SELECT
AVG(if (order_type = "immediate", 1, 0))
FROM
cte;

Monthly Transactions

1193. Monthly Transactions I

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
DROP TABLE if EXISTS Transactions;
CREATE TABLE
If NOT EXISTS Transactions (
id INT,
country VARCHAR(4),
state enum ('approved', 'declined'),
amount INT,
trans_date DATE
);
TRUNCATE TABLE Transactions;
INSERT INTO
Transactions (id, country, state, amount, trans_date)
VALUES
('121', 'US', 'approved', '1000', '2018-12-18');
INSERT INTO
Transactions (id, country, state, amount, trans_date)
VALUES
('122', 'US', 'declined', '2000', '2018-12-19');
INSERT INTO
Transactions (id, country, state, amount, trans_date)
VALUES
('123', 'US', 'approved', '2000', '2019-01-01');
INSERT INTO
Transactions (id, country, state, amount, trans_date)
VALUES
('124', 'DE', 'approved', '2000', '2019-01-07');
1
2
3
4
5
6
7
8
SELECT DATE_FORMAT(trans_date, '%Y-%m') AS `month`,
country,
COUNT(*) AS trans_count,
SUM(state = 'approved') AS approved_count,
SUM(amount) AS trans_total_amount,
SUM(IF(state = 'approved', amount, 0)) AS approved_total_amount
FROM Transactions
GROUP BY 1, 2

1205. Monthly Transactions II

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
DROP TABLE if EXISTS Transactions;
DROP TABLE if EXISTS Chargebacks;
CREATE TABLE
If NOT EXISTS Transactions (
id INT,
country VARCHAR(4),
state enum ('approved', 'declined'),
amount INT,
trans_date DATE
);
CREATE TABLE
If NOT EXISTS Chargebacks (trans_id INT, trans_date DATE);
TRUNCATE TABLE Transactions;
INSERT INTO
Transactions (id, country, state, amount, trans_date)
VALUES
('101', 'US', 'approved', '1000', '2019-05-18');
INSERT INTO
Transactions (id, country, state, amount, trans_date)
VALUES
('102', 'US', 'declined', '2000', '2019-05-19');
INSERT INTO
Transactions (id, country, state, amount, trans_date)
VALUES
('103', 'US', 'approved', '3000', '2019-06-10');
INSERT INTO
Transactions (id, country, state, amount, trans_date)
VALUES
('104', 'US', 'declined', '4000', '2019-06-13');
INSERT INTO
Transactions (id, country, state, amount, trans_date)
VALUES
('105', 'US', 'approved', '5000', '2019-06-15');
TRUNCATE TABLE Chargebacks;
INSERT INTO
Chargebacks (trans_id, trans_date)
VALUES
('102', '2019-05-29');
INSERT INTO
Chargebacks (trans_id, trans_date)
VALUES
('101', '2019-06-30');
INSERT INTO
Chargebacks (trans_id, trans_date)
VALUES
('105', '2019-09-18');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
WITH
cte AS (
SELECT
id,
country,
state,
amount,
date_format (trans_date, '%Y-%m') AS `month`
FROM
Transactions
WHERE
state = 'approved'
UNION ALL
SELECT
trans_id AS id,
country,
'chargeback' AS state,
amount,
date_format (c.trans_date, '%Y-%m') AS `month`
FROM
Chargebacks c
LEFT JOIN Transactions t ON c.trans_id = t.id
)
SELECT
`month`,
country,
SUM(if (state = 'approved', 1, 0)) AS approved_count,
SUM(if (state = 'approved', amount, 0)) AS approved_amount,
SUM(if (state = 'chargeback', 1, 0)) AS chargeback_count,
SUM(if (state = 'chargeback', amount, 0)) AS chargeback_amount
FROM
cte
GROUP BY
1,
2
ORDER BY
1,
2;

Social Network

602. Friend Requests II: Who Has the Most Friends

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
DROP TABLE if EXISTS RequestAccepted;
CREATE TABLE
If NOT EXISTS RequestAccepted (requester_id INT NOT NULL, accepter_id INT NULL, accept_date DATE NULL);
TRUNCATE TABLE RequestAccepted;
INSERT INTO
RequestAccepted (requester_id, accepter_id, accept_date)
VALUES
('1', '2', '2016-06-03');
INSERT INTO
RequestAccepted (requester_id, accepter_id, accept_date)
VALUES
('1', '3', '2016-06-08');
INSERT INTO
RequestAccepted (requester_id, accepter_id, accept_date)
VALUES
('2', '3', '2016-06-08');
INSERT INTO
RequestAccepted (requester_id, accepter_id, accept_date)
VALUES
('3', '4', '2016-06-09');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
WITH
cte1 AS (
SELECT
requester_id AS id
FROM
RequestAccepted
UNION ALL
SELECT
accepter_id AS id
FROM
RequestAccepted
),
cte2 AS (
SELECT
id,
COUNT(*) AS num,
DENSE_RANK() OVER (
ORDER BY
COUNT(*) desc
) AS rnk
FROM
cte1
GROUP BY
1
)
SELECT
id,
num
FROM
cte2
WHERE
rnk = 1;