8000 more solutions · dana3601/Leetcode-Problems@d2fd13c · GitHub
[go: up one dir, main page]
Skip to content

Commit d2fd13c

Browse files
Qian-Yu-2020Qian-Yu-2020
committed
more solutions
1 parent fed92fa commit d2fd13c

10 files changed

+488
-2
lines changed

‎Customers or Users/# 1407. Top Travellers.sql

Lines changed: 75 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -8,3 +8,78 @@ LEFT JOIN Rides r ON u.id = r.user_id
88
GROUP BY u.id
99
ORDER BY travelled_distance DESC, name ASC
1010
;
11+
12+
13+
14+
select
15+
u.name,
16+
ifnull(r.tot,0) as travelled_distance
17+
from
18+
Users u
19+
left join
20+
(select user_id, sum(distance) as tot
21+
from Rides
22+
group by user_id) r
23+
on
24+
u.id = r.user_id
25+
order by
26+
r.tot desc,
27+
u.name
28+
29+
30+
31+
32+
select u.name, ifnull(sum(r.distance), 0) as travelled_distance
33+
from users u
34+
left join rides r
35+
on u.id = r.user_id
36+
group by r.user_id
37+
order by travelled_distance desc, u.name asc
38+
39+
40+
41+
42+
select u.name, coalesce(sum(r.distance),0) as "travelled_distance"
43+
from users as u
44+
left join rides as r
45+
on u.id = r.user_id
46+
group by u.name
47+
order by travelled_distance desc, u.name
48+
49+
50+
51+
52+
53+
select name, sum(ifnull(distance,0)) as travelled_distance
54+
from rides r
55+
right join users u
56+
on r.user_id = u.id
57+
group by name
58+
order by 2 desc,1 asc;
59+
60+
61+
62+
63+
64+
65+
66+
67+
68+
69+
70+
71+
72+
73+
74+
75+
76+
77+
78+
79+
80+
81+
82+
83+
84+
85+

‎Customers or Users/# 1454. Active Users.sql

Lines changed: 60 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,4 +8,63 @@ WHERE a.id=b.id AND
88
DATEDIFF(a.login_date,b.login_date) BETWEEN 1 AND 4
99
GROUP BY a.id, a.login_date
1010
HAVING COUNT(DISTINCT b.login_date)=4
11-
;
11+
;
12+
13+
14+
15+
SELECT DISTINCT a.id, a.name
16+
FROM (
17+
SELECT
18+
id, LEAD(login_date, 4) OVER (PARTITION BY id ORDER BY login_date) AS five_days,
19+
DATEDIFF(LEAD(login_date, 4) OVER (PARTITION BY id ORDER BY login_date), login_date) AS gap
20+
FROM (SELECT DISTINCT id, login_date FROM Logins) c
21+
) d
22+
LEFT JOIN Accounts a ON a.id = d.id
23+
WHERE gap = 4
24+
ORDER BY a.id
25+
26+
27+
28+
29+
SELECT *
30+
FROM Accounts
31+
WHERE id IN
32+
(SELECT DISTINCT t1.id
33+
FROM Logins t1 INNER JOIN Logins t2 on t1.id = t2.id AND DATEDIFF(t1.login_date, t2.login_date) BETWEEN 1 AND 4
34+
GROUP BY t1.id, t1.login_date
35+
HAVING COUNT(DISTINCT(t2.login_date)) = 4)
36+
ORDER BY id
37+
38+
39+
40+
41+
WITH temp AS (SELECT l1.id, l1.login_date, COUNT(DISTINCT l2.login_date) AS cnt
42+
FROM Logins l1
43+
LEFT JOIN Logins l2 ON l1.id = l2.id AND DATEDIFF(l2.login_date, l1.login_date) BETWEEN 1 AND 4
44+
GROUP BY 1,2
45+
HAVING cnt>=4)
46+
47+
SELECT DISTINCT temp.id, name
48+
FROM temp
49+
JOIN Accounts ON temp.id = Accounts.id
50+
ORDER BY 1;
51+
52+
53+
54+
55+
56+
57+
58+
59+
60+
61+
62+
63+
64+
65+
66+
67+
68+
69+
70+

‎Marketing and Ecommerce/# 1421. NPV Queries.sql

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -6,3 +6,24 @@ SELECT Queries.id AS id, Queries.year, IFNULL(NPV.npv,0) AS npv
66
FROM Queries LEFT JOIN NPV USING(id,year)
77
ORDER BY id
88
;
9+
10+
11+
select q.id, q.year, ifnull(npv, 0) npv
12+
from queries q left join npv n on
13+
q.id = n.id and q.year = n.year
14+
15+
16+
17+
select q.id, q.year, coalesce(n.npv, 0) as npv
18+
from npv n right join queries q
19+
on n.id = q.id and n.year = q.year
20+
21+
22+
23+
select t1.id, t1.year, ifnull(npv, 0) as npv
24+
from queries as t1
25+
left join NPV as t2
26+
on t1.id = t2.id and t1.year = t2.year;
27+
28+
29+

‎Order and Deliver/# 1398. Customers Who Bought Products A B not C.sql

Lines changed: 47 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -9,3 +9,50 @@ HAVING SUM(o.product_name = "A") >0
99
AND SUM(o.product_name = "B") >0
1010
AND SUM(o.product_name = "C") = 0
1111
;
12+
13+
14+
15+
select c.customer_id, c.customer_name
16+
from Customers as c
17+
inner join
18+
(select customer_id,
19+
sum(CASE
20+
WHEN product_name = 'A' THEN 1
21+
WHEN product_name = 'B' THEN 1
22+
WHEN product_name = 'C' THEN -1
23+
ELSE 0 END) as tot
24+
from Orders
25+
group by customer_id
26+
having tot > 1) as o
27+
where c.customer_id = o.customer_id
28+
29+
30+
31+
32+
SELECT c.customer_id, c.customer_name
33+
FROM Customers c
34+
JOIN Orders a ON a.customer_id = c.customer_id AND a.product_name = "A"
35+
JOIN Orders b ON b.customer_id = c.customer_id AND b.product_name = "B"
36+
LEFT OUTER JOIN Orders o on o.customer_id = c.customer_id AND o.product_name = "C"
37+
WHERE o.order_id IS NULL
38+
39+
40+
41+
42+
select distinct customer_id, customer_name
43+
from Customers
44+
where customer_id in
45+
(
46+
select customer_id
47+
from Orders
48+
where product_name='A'
49+
) and customer_id in
50+
(
51+
select customer_id
52+
from Orders
53+
where product_name='B'
54+
) and customer_id not in
55+
(
56+
select customer_id
57+
from Orders
58+
where product_name='C'

‎Order and Deliver/# 1445. Apples & Oranges.sql

Lines changed: 79 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -6,4 +6,82 @@
66
SELECT sale_date,
77
SUM(CASE WHEN fruit = "apples" THEN sold_num ELSE (-sold_num) END) AS diff
88
FROM Sales
9-
GROUP BY sale_date
9+
GROUP BY sale_date
10+
11+
12+
13+
SELECT a.sale_date sale_date, num_apples - num_oranges as diff
14+
FROM
15+
# Create table for Apples
16+
(SELECT sale_date, sold_num as num_apples
17+
FROM Sales
18+
WHERE fruit = 'apples') a,
19+
# Create table for Oranges
20+
(SELECT sale_date, sold_num as num_oranges
21+
FROM Sales
22+
WHERE fruit = 'oranges') o
23+
WHERE
24+
# Join on sale_date
25+
o.sale_date = a.sale_date
26+
ORDER BY 1
27+
28+
29+
30+
31+
SELECT a.sale_date, (a.sold_num- b.sold_num) as diff
32+
from Sales a, Sales b
33+
where a.sale_date = b.sale_date and a.fruit='apples' and a.fruit!=b.fruit
34+
order by a.sale_date
35+
36+
37+
38+
select sale_date, sum(if(fruit = 'apples', sold_num, -sold_num)) diff
39+
from Sales
40+
group by sale_date;
41+
42+
43+
44+
select a.sale_date, (a.sold_num- b.sold_num) as diff from
45+
(select sale_date, sold_num from Sales where fruit='apples') as a
46+
join
47+
(select sale_date, sold_num from Sales where fruit='oranges') as b
48+
on a.sale_date = b.sale_date
49+
50+
51+
52+
53+
with apple as
54+
(select sale_date, sold_num from Sales where fruit='apples' group by sale_date),
55+
56+
oranges as
57+
(select sale_date, sold_num from Sales where fruit='oranges' group by sale_date)
58+
59+
select a.sale_date, (a.sold_num-o.sold_num) as diff
60+
from apple a join oranges o
61+
on a.sale_date=o.sale_date
62+
63+
64+
65+
66+
67+
68+
69+
70+
71+
72+
73+
74+
75+
76+
77+
78+
79+
80+
81+
82+
83+
84+
85+
86+
87+

‎Order and Deliver/# 1484. Group Sold Products By The Date.sql

Lines changed: 49 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -8,3 +8,52 @@ SELECT DISTINCT sell_date, COUNT(DISTINCT product) AS num_sold,
88
FROM Activities
99
GROUP BY sell_date
1010
;
11+
12+
13+
select sell_date, count(distinct product) as num_sold,
14+
group_concat(distinct product order by product ASC separator ',') as products from Activities
15+
group by sell_date
16+
17+
18+
SELECT sell_date,
19+
COUNT(DISTINCT(product)) AS num_sold,
20+
GROUP_CONCAT(DISTINCT product ORDER BY product ASC SEPARATOR ',') AS products
21+
FROM Activities
22+
GROUP BY sell_date
23+
ORDER BY sell_date ASC
24+
25+
26+
27+
select sell_date, count(distinct product) as num_sold ,
28+
group_concat(distinct product order by product asc separator ',') as products
29+
from activities
30+
group by sell_date
31+
order by sell_date
32+
33+
34+
35+
select
36+
sell_date,
37+
COUNT(product) num_sold,
38+
group_concat(product order by product) products
39+
from (SELECT DISTINCT * FROM Activities) Activities
40+
group by sell_date
41+
order by sell_date
42+
43+
44+
45+
46+
select
47+
sell_date, COUNT(DISTINCT product) num_sold,
48+
group_concat(DISTINCT product) products
49+
from Activities
50+
group by sell_date
51+
order by sell_date
52+
53+
54+
55+
56+
57+
58+
59+

‎Others /# 1440. Evaluate Boolean Expression.sql

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -17,3 +17,19 @@ ON e.left_operand = v1.name
1717
JOIN Variables v2
1818
ON e.right_operand = v2.name
1919

20+
21+
22+
23+
SELECT left_operand, operator, right_operand,
24+
(CASE WHEN (e.operator='>' AND v1.value > v2.value)
25+
OR (e.operator='=' AND v1.value = v2.value)
26+
OR (e.operator='<' AND v1.value < v2.value)
27+
THEN 'true'
28+
ELSE 'false'
29+
END) value
30+
FROM Expressions e, Variables v1, Variables v2
31+
WHERE e.left_operand = v1.name AND e.right_operand = v2.name
32+
33+
34+
35+

0 commit comments

Comments
 (0)
0