leetcode

6 Database Problems

7 files changed, 53 insertions(+), 0 deletions(-)

diff --git a/ Problems/0550.sql b/ Problems/0550.sql

@@ -0,0 +1,8 @@

SELECT ROUND(COUNT(DISTINCT player_id) / (SELECT COUNT(DISTINCT player_id) FROM Activity), 2) AS fraction

FROM Activity

WHERE (player_id, DATE_SUB(event_date, INTERVAL 1 DAY)) IN (

SELECT player_id, MIN(event_date) as first_date

FROM Activity

GROUP BY player_id

)

diff --git a/ Problems/0570.sql b/ Problems/0570.sql

@@ -0,0 +1,8 @@

SELECT name

FROM Employee E

WHERE id IN (

SELECT managerId

FROM Employee

GROUP BY managerId

HAVING COUNT(managerId) >= 5

)

diff --git a/ Problems/1174.sql b/ Problems/1174.sql

@@ -0,0 +1,7 @@

SELECT ROUND(100 * AVG(order_date = customer_pref_delivery_date), 2) AS immediate_percentage

FROM Delivery

WHERE (customer_id, order_date) IN (

SELECT customer_id, MIN(order_date) as first_order

FROM Delivery

GROUP BY customer_id

)

diff --git a/ Problems/1193.sql b/ Problems/1193.sql

@@ -0,0 +1,8 @@

SELECT LEFT(trans_date, 7) 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 YEAR(trans_date), MONTH(trans_date), country

diff --git a/ Problems/1393.sql b/ Problems/1393.sql

@@ -0,0 +1,10 @@

SELECT

stock_name,

SUM(

CASE

WHEN operation = 'buy' THEN -price

WHEN operation = 'sell' THEN price

END

) AS capital_gain_loss

FROM Stocks

GROUP BY stock_name

diff --git a/ Problems/1934.sql b/ Problems/1934.sql

@@ -0,0 +1,6 @@

SELECT S.user_id,

ROUND(AVG(if(C.action="confirmed", 1, 0)), 2) AS confirmation_rate

FROM Signups AS S

LEFT JOIN Confirmations AS C

ON S.user_id = C.user_id

GROUP BY user_id;

diff --git a/ README.md b/ README.md

@@ -344,6 +344,7 @@ for solving problems. | 0542 | Medium | [01 Matrix](Problems/0542.cpp) | | 0543 | Easy | [Diameter of Binary Tree](Problems/0543.cpp) | | 0547 | Medium | [Number of Provinces](Problems/0547.cpp) |

| 0550 | Medium | [Game Play Analysis IV](Problems/0550.cpp) |

| 0553 | Medium | [Optimal Division](Problems/0553.cpp) | | 0556 | Medium | [Next Greater Element III](Problems/0556.cpp) | | 0557 | Easy | [Reverse Words in a String III](Problems/0557.cpp) |

@@ -353,6 +354,7 @@ for solving problems. | 0563 | Easy | [Binary Tree Tilt](Problems/0563.cpp) | | 0566 | Easy | [Reshape the Matrix](Problems/0566.cpp) | | 0567 | Medium | [Permutation in String](Problems/0567.cpp) |

| 0570 | Medium | [Managers with at Least 5 Direct Reports](Problems/0570.cpp) |

| 0572 | Easy | [Subtree of Another Tree](Problems/0572.cpp) | | 0577 | Easy | [Employee Bonus](Problems/0577.cpp) | | 0583 | Medium | [Delete Operation for Two Strings](Problems/0583.cpp) |

@@ -571,9 +573,11 @@ for solving problems. | 1161 | Medium | [Maximum Level Sum of a Binary Tree](Problems/1161.cpp) | | 1162 | Medium | [As Far from Land as Possible](Problems/1162.cpp) | | 1170 | Medium | [Compare Strings by Frequency of the Smallest Character](Problems/1170.cpp) |

| 1174 | Medium | [Immediate Food Delivery II](Problems/1174.cpp) |

| 1179 | Easy | [Reformat Department Table](Problems/1179.cpp) | | 1187 | Hard | [Make Array Strictly Increasing](Problems/1187.cpp) | | 1190 | Medium | [Reverse Substrings Between Each Pair of Parentheses](Problems/1190.cpp) |

| 1193 | Medium | [Monthly Transactions I](Problems/1193.cpp) |

| 1202 | Medium | [Smallest String With Swaps](Problems/1202.cpp) | | 1203 | Hard | [Sort Items by Groups Respecting Dependencies](Problems/1203.cpp) | | 1207 | Easy | [Unique Number of Occurrences](Problems/1207.cpp) |

@@ -650,6 +654,7 @@ for solving problems. | 1381 | Medium | [Design a Stack With Increment Operation](Problems/1381.cpp) | | 1382 | Medium | [Balance a Binary Search Tree](Problems/1382.cpp) | | 1387 | Medium | [Sort Integers by The Power Value](Problems/1387.cpp) |

| 1393 | Medium | [Capital Gain/Loss](Problems/1393.cpp) |

| 1395 | Medium | [Count Number of Teams](Problems/1395.cpp) | | 1396 | Medium | [Design Underground System](Problems/1396.cpp) | | 1400 | Medium | [Construct K Palindrome Strings](Problems/1400.cpp) |

@@ -822,6 +827,7 @@ for solving problems. | 1921 | Medium | [Eliminate Maximum Number of Monsters](Problems/1921.cpp) | | 1926 | Medium | [Nearest Exit from Entrance in Maze](Problems/1926.cpp) | | 1930 | Medium | [Unique Length-3 Palindromic Subsequences](Problems/1930.cpp) |

| 1934 | Medium | [Confirmation Rate](Problems/1934.cpp) |

| 1947 | Medium | [Maximum Compatibility Score Sum](Problems/1947.cpp) | | 1962 | Medium | [Remove Stones to Minimize the Total](Problems/1962.cpp) | | 1963 | Medium | [Minimum Number of Swaps to Make the String Balanced](Problems/1963.cpp) |