2.0_leetcode_database_practice

EASY tag:

176.

SELECT IFNULL(
    (SELECT ...
    LIMIT 1 OFFSET 1,NULL
) AS SecondHighestSalary


SELECT MAX(salary) AS ..
FROM
WHERE Salary < (SELECT Max(Salary) FROM Employee)

181.

SELECT a.Name FROM Employee a
JOIN Employee b
ON a.Id = b.ManagerId AND a.Salary > b.Salary

Tips: self-join classical questions

182.

Having count()>1

196.delete duplicate

DELETE p1 FROM Person p1, Person p2
WHERE p1.Email= p2.Email and p1.ID > P2.ID;

620.

mod(id,2)=1

627.

UPDATE salary
SET
    sex = CASE sex
          WHEN 'm' THEN 'f'
          ELSE 'm'
          END;

1173.

SELECT ROUND(100*SUM(ORDER DATE = DELIVERY DATE)/COUNT(*),2)

1179. reformat

SELECT id, SUM(if (month = 'Jan', revenue, null)) AS Jan_Revenue

1251.

select 
        p.product_id ,
        ROUND((SUM(u.units*p.price)/SUM(units)),2) average_price 

from Prices p 
JOIN UnitsSold u 
ON 
        p.product_id = u.product_id 
        AND u.purchase_date between p.start_date and p.end_date 
group by p.product_id

1303.

SELECT 
    employee_id, 
    team_size
FROM
    Employee a
JOIN
    (SELECT team_id, COUNT(*) AS team_size FROM Employee GROUP BY team_id) b
ON a.team_id = b.team_id; 

1322.

select ad_id, 
ifnull(round(sum(case when action = 'Clicked' then 1 else 0 end) / sum(case when action = 'Clicked' or action = 'Viewed' then 1 else 0 end) * 100, 2), 0) as ctr
from Ads
group by ad_id
order by ctr desc, ad_id asc

1484

select sell_date, count(distinct product) as num_sold , 
group_concat(DISTINCT product ORDER BY product ASC separator ',') as products               
from activities  
group by sell_date
order by sell_date

Tips: The GROUP_CONCAT() function in MySQL is used to concatenate data from multiple rows into one field. This is an aggregate (GROUP BY) function which returns a String value, if the group contains at least one non-NULL value. Otherwise, it returns NULL.

1453.Create a session bar chart

(select '[0-5>' as bin, sum(case when duration/60 < 5 then 1 else 0 end) as total from sessions)
union
(select '[5-10>' as bin, sum(case when ((duration/60 >= 5) and (duration/60 < 10)) then 1 else 0 end) as total from sessions)
union
(select '[10-15>' as bin, sum(case when ((duration/60 >= 10) and (duration/60 < 15)) then 1 else 0 end) as total from sessions)
union
(select '15 or more' as bin, sum(case when duration/60 >= 15 then 1 else 0 end) as total from sessions)

1543.

SELECT TRIM(lower(product_name)) AS product_name, DATE_FORMAT(sale_date,'%Y-%m') AS sale_date,count(*)AS total
FROM Sales
GROUP BY 1,2
ORDER BY 1,2

TIPS:

• The TRIM() removes the space character
• LTRIM() removes leading spaces from a string.
• RTRIM() removes trailing spaces from a string:
• DATE_FORMAT

%D	Day of the month as a numeric value, followed by suffix (1st, 2nd, 3rd, …)
%d	Day of the month as a numeric value (01 to 31)
—-	—————————————-
%M	Month name in full (January to December)
—-	—————————————-

1565.

SELECT DATE_FORMAT(order_date, '%Y-%m') AS month,count(DISTINCT order_id) AS order_count,count(DISTINCT customer_id) AS customer_count
FROM Orders
WHERE invoice > 20
GROUP BY 1
ORDER BY 1

TIPS: LEFT(order_date, 7) month

Medium tag

177.

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  DECLARE p INT;
  SET p = N - 1;
  RETURN (
      SELECT DISTINCT Salary
      FROM Employee
      ORDER BY Salary DESC
      LIMIT 1 OFFSET p
  );
END

178.

select 
score,
dense_rank() over(order by score desc) as rank
from scores

Row_number() RANK() Dense_rank()

184.

SELECT
    Department.name AS 'Department',
    Employee.name AS 'Employee',
    Salary
FROM
    Employee
JOIN
    Department 
ON Employee.DepartmentId = Department.Id
WHERE
    (Employee.DepartmentId , Salary) IN
    (   SELECT
            DepartmentId, MAX(Salary)
        FROM
            Employee
        GROUP BY DepartmentId
    )

Tips:

since there might be more than one people in each department has the same salary, it is safe to just select the department id and max salary firstly.

534.

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
Order by player_id;

550.

SELECT ROUND(COUNT(t2.player_id)/COUNT(t1.player_id),2) AS fraction
FROM
(SELECT player_id, MIN(event_date) AS first_login FROM Activity GROUP BY player_id) t1 
LEFT JOIN Activity t2
ON t1.player_id = t2.player_id AND t1.first_login = t2.event_date - 1

578.

SELECT question_id
FROM survey_log 
GROUP BY qustion_id
ORDER BY COUNT(answer_id)/COUNT(IF(action = 'SHOW',1,NULL)) DESC
LIMIT 1

580.

SELECT
    SUM(insurance.TIV_2016) AS TIV_2016
FROM
    insurance
WHERE
    insurance.TIV_2015 IN
    (
      SELECT
        TIV_2015
      FROM
        insurance
      GROUP BY TIV_2015
      HAVING COUNT(*) > 1
    )
    AND CONCAT(LAT, LON) IN
    (
      SELECT
        CONCAT(LAT, LON)
      FROM
        insurance
      GROUP BY LAT , LON
      HAVING COUNT(*) = 1
    )

608.

SELECT
    id AS Id,
    CASE
        WHEN tree.P_id IS NULL
          THEN 'Root'
        WHEN tree.id IN (SELECT atree.p_id FROM tree atree)
          THEN 'Inner'
        ELSE 'Leaf'
    END AS Type
FROM
    tree
ORDER BY `Id`
;

612.

SELECT
    ROUND(SQRT(MIN((POW(p1.x - p2.x, 2) + POW(p1.y - p2.y, 2)))),2) AS shortest
FROM
    point_2d p1
        JOIN
    point_2d p2 ON p1.x != p2.x OR p1.y != p2.y

626.

SELECT
(CASE WHEN MOD(id,2) != 0 AND counts != id THEN id+1
          MOD(id,2) != 0 AND counts == id THEN id
          ELSE id - 1
END) AS id, student
FROM seat,(SELECT COUNT(*) as counts FROM seat) AS seat_counts
ORDER BY id ASC

1077.

SELECT t.project_id, t.employee_id
FROM (
    SELECT p1.project_id, p1.employee_id, 
        DENSE_RANK() OVER (PARTITION BY p1.project_id ORDER BY e1.experience_years DESC) AS r
    FROM Project p1 JOIN Employee e1 ON p1.employee_id = e1.employee_id
) AS t
WHERE t.r = 1

1098.

SELECT b.book_id, b.name 
FROM Books b
LEFT JOIN Orders o
ON b.book_id = o.book_id AND DATEDIFF('2019-06-23',available_from) > 30
GROUP BY b.book_id, b.name
HAVING IFNULL(SUM(quantity),0)< 10

1107.

SELECT login_date, count(user_id) as user_count
FROM(
    SELECT user_id, MIN(activity_date) AS login_date
    FROM Traffic
    WHERE activity = 'login'
    GROUP BY user_id
    ) t
WHERE datediff('2019-6-30', login_date) <= 90
GROUP BY login_date

1112.

SELECT student_id, MIN(course_id) AS course_id,grade FROM enrollments
WHERE(student_id, grade) IN(
SELECT student_id, MAX(grade) as ma FROM Enrollments GROUP BY student_id
) 
GROUP BY student_id, grade
ORDER BY student_id

1126.

SELECT business_id FROM
(SELECT *, AVG(OCCURENCES) OVER (PARTITION BY event_type) AS e_AVG FROM events)a 
WHERE OCCURENCES > e_avg
GROUP BY business_id
HAVING COUNT(event_type) > 1

1132.

select round(avg(num_remove / num_spam) * 100, 2) as average_daily_percent
from
(
    select action_date, 
           count(distinct a.post_id) as num_spam,
           count(distinct case when remove_date is not null then a.post_id 
                               else null 
                          end) as num_remove
    from Actions a left join Removals r
    on a.post_id = r.post_id
    where extra = 'spam'
    group by action_date
) t

1164.

SELECT distinct a.product_id,ifnull(temp.new_price,10) as price 
FROM products as a
LEFT JOIN
(SELECT * 
FROM products 
WHERE (product_id, change_date) in (select product_id,max(change_date) from products where change_date<="2019-08-16" group by product_id)) as temp
on a.product_id = temp.product_id;

1174.

SELECT
    ROUND(100*SUM(CASE WHEN order_date = customer_pref_delivery_date THEN 1
    ELSE 0 END)/ COUNT(distinct customer_id) ,2) AS immediate_percentage
FROM
    Delivery
WHERE
    (customer_id, order_date)
IN
(SELECT
    customer_id, min(order_date) as min_date
FROM
    Delivery
GROUP BY
    customer_id
);

1204.

select q1.person_name
from queue as q1 
join
(select turn, sum(weight) over (order by turn) as cumu_sum
 from queue) as temp1
on q1.turn = temp1.turn
where temp1.cumu_sum <= 1000
order by temp1.cumu_sum desc
limit 1;

tips: RUNNING TOTAL

1205.

SELECT month, country, 
    SUM(CASE WHEN state = "approved" THEN 1 ELSE 0 END) AS approved_count, 
    SUM(CASE WHEN state = "approved" THEN amount ELSE 0 END) AS approved_amount, 
    SUM(CASE WHEN state = "back" THEN 1 ELSE 0 END) AS chargeback_count,
    SUM(CASE WHEN state = "back" THEN amount ELSE 0 END) AS chargeback_amount
FROM
(
    SELECT LEFT(chargebacks.trans_date, 7) AS month,
        country, 
        "back" AS state, amount
    FROM chargebacks
    JOIN transactions ON chargebacks.trans_id = transactions.id
    UNION ALL
    SELECT LEFT(trans_date, 7) AS month, country, state, amount
    FROM transactions
    WHERE state = "approved"
) s
GROUP BY month, country

The UNION command combines the result set of two or more SELECT statements (only distinct values)

The UNION ALL command combines the result set of two or more SELECT statements (allows duplicate values).

1212.

SELECT team_id, team_name,
SUM(
    CASE WHEN team_id = host_team AND host_goals > guest_goals THEN 3
         WHEN team_id = guest_team AND guest_goals > host_goals THEN 3
         WHEN host_goals = guest_goals THEN 1
         ELSE 0
    END          
) AS "num_points"
FROM Teams t
LEFT JOIN Matches m ON t.team_id = m.host_team OR t.team_id = m.guest_team
GROUP BY team_id, team_name
ORDER BY num_points DESC, team_id

Key point: LEFT JOIN Matches m ON t.team_id = m.host_team OR t.team_id = m.guest_team

1264.

SELECT DISTINCT page_id AS recommended_page
FROM (
SELECT CASE WHEN user1_id = 1 THEN user2_id
            WHEN user2_id = 1 THEN user1_id 
      END AS user_id
FROM Friendship) a
JOIN Likes l
ON a.user_id = l.user_id
WHERE page_id NOT IN (SELECT page_id FROM Likes WHERE user_id = 1)

1270.

SELECT e1.employee_id
FROM Employees e1
JOIN Employees e2
ON e1.manager_id = e2.employee_id
JOIN Employees e3
ON e2.manager_id = e3.employee_id
WHERE e3.manager_id = 1 AND e1.employee_id != 1

1285.

SELECT MIN(log_id)AS start_id, MAX(log_id) AS end_id
FROM 
(SELECT log_id, ROW_NUMBER() OVER(Order by log_id) as num
FROM  LOGs)a
GROUP BY log_id - num

TIPS:
log_id, num, difference
1, 1, 0
2, 2, 0
3, 3, 0
7, 4, 3
8, 5, 3
10, 6, 4

1321.

SELECT a.visited_on as visted_on, SUM(b.day_sum) AS amount, ROUND(b.day_sum,2) AS average_amount 
FROM (SELECT visited_on, SUM(amount) AS day_sum FROM Customer GROUP BY visited_on) a,
(SELECT visited_on, SUM(amount) AS day_sum FROM Customer GROUP BY visited_on)b
WHERE DATEDIFF(a.visited_on, b.visited_on) BETWEEN 0 AND 6
GROUP BY a.visited_on
HAVING COUNT(b.visited_on) = 7

TIPS : moving average and sum every 7 days

1364.

select
    i.invoice_id,
    c.customer_name,
    i.price,
    count(con.user_id) as contacts_cnt,
    count(c2.email) as trusted_contacts_cnt
from invoices i
join customers c on c.customer_id = i.user_id
left join contacts con on con.user_id = c.customer_id
left join customers c2 on c2.email = con.contact_email
group by i.invoice_id, c.customer_name, i.price
order by i.invoice_id

1398.

SELECT customer_id,customer_name FROM Customers
WHERE customer_id IN (
SELECT customer_id
FROM Orders 
GROUP BY customer_id
HAVING SUM(product_name = 'A') > 0 AND SUM(product_name = 'B') > 0 AND SUM(product_name = 'C') = 0)
ORDER BY 1

1440.

SELECT e.left_operand, e.operator, e.right_operand,
    (
        CASE
            WHEN e.operator = '<' AND v1.value < v2.value THEN 'true'
            WHEN e.operator = '=' AND v1.value = v2.value THEN 'true'
            WHEN e.operator = '>' AND v1.value > v2.value THEN 'true'
            ELSE 'false'
        END
    ) AS value
FROM Expressions e
JOIN Variables v1 ON e.left_operand = v1.name
JOIN Variables v2 ON e.right_operand = v2.name

1445.

SELECT *
FROM Accounts
WHERE id IN
    (SELECT DISTINCT t1.id 
     FROM Logins t1 INNER JOIN Logins t2 on t1.id = t2.id AND DATEDIFF(t1.login_date, t2.login_date) BETWEEN 1 AND 4
     GROUP BY t1.id, t1.login_date
     HAVING COUNT(DISTINCT(t2.login_date)) = 4)
ORDER BY id

Tips: 5 consecutive days

1459.

SELECT  pt1.id as P1, pt2.id as P2,
        ABS(pt2.x_value - pt1.x_value)*ABS(pt2.y_value-pt1.y_value) as AREA
FROM Points pt1 JOIN Points pt2 
ON pt1.id<pt2.id
AND pt1.x_value!=pt2.x_value 
AND pt2.y_value!=pt1.y_value
ORDER BY AREA DESC, p1 ASC, p2 ASC;

1468.

SELECT s.company_id, s.employee_id, s.employee_name,
ROUND(
        case when x.max_sal between 1000 and 10000 then salary * 0.76
        when x.max_sal > 10000 then salary * 0.51 else salary end, 0) AS salary

FROM salaries s 
JOIN
(SELECT company_id, MAX(salary) max_sal FROM salaries GROUP BY company_id) x
ON s.company_id = x.company_id;

TIP:

max() group by only gives you one row

1501.

SELECT Country.name AS country
FROM Person JOIN Calls ON Calls.caller_id = Person.id OR Calls.callee_id = Person.id
JOIN Country ON Country.country_code = LEFT(Person.phone_number, 3)
GROUP BY Country.name
HAVING AVG(duration) > (SELECT AVG(duration) FROM Calls)

1532.

SELECT customer_name, customer_id, order_id, order_date
FROM 
 (
   SELECT name AS customer_name, Customers.customer_id, order_id, order_date, 
     (row_number() over (partition by Customers.customer_id order by order_date DESC)) AS rownum
   FROM Customers
   INNER JOIN Orders ON Customers.customer_id = Orders.customer_id
   ORDER BY customer_name, Customers.customer_id, order_date DESC
) latest_orders
WHERE rownum <= 3

1555.

SELECT user_id,user_name,
IFNULL(SUM(CASE WHEN a.user_id=b.paid_by THEN -amount ELSE amount END),0)+a.credit as credit,
CASE WHEN IFNULL(SUM(CASE WHEN a.user_id=b.paid_by THEN -amount ELSE amount END),0)>=-a.credit THEN "No" ELSE "Yes" END as credit_limit_breached
FROM Users as a
LEFT JOIN Transactions as b
ON a.user_id=b.paid_by OR a.user_id=b.paid_to
GROUP BY a.user_id;

HARD tag

185.

select 
    Department, Employee, Salary

from
(
select
    D.name as Department, E.name as Employee, E.salary as Salary,
    dense_rank() over (partition by DepartmentId order by salary desc) as salary_rank
from
    employee E join Department D on E.departmentid = D.id
) as new_table

where
    new_table.salary_rank <= 3

262.

SELECT Request_at as Day, 
        ROUND ((SUM(        
        CASE WHEN Status = 'cancelled_by_driver' 
                    OR Status = 'cancelled_by_client' 
                THEN 1 
                ELSE 0 
        END)
        / COUNT(Request_at)) ,2) as "Cancellation Rate"                               
FROM Trips trp
WHERE Request_at BETWEEN '2013-10-01' AND '2013-10-03' 
      AND trp.Client_Id IN (SELECT Users_Id FROM Users WHERE Banned = 'No')
      AND trp.Driver_Id IN (SELECT Users_Id FROM Users WHERE Banned = 'No')
GROUP BY Request_at

569.(got medium by partition)

SELECT Id, Company, Salary
FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY COMPANY ORDER BY Salary ASC, Id ASC) AS RN_ASC,
ROW_NUMBER() OVER(PARTITION BY COMPANY ORDER BY Salary DESC, Id DESC) AS RN_DESC
FROM Employee) AS temp
WHERE RN_ASC BETWEEN RN_DESC - 1 AND RN_DESC + 1
ORDER BY Company, Salary;

579.

Select Id , Month , 
sum(Salary) over(Partition by Id order by month Rows 2 preceding) AS Salary
FROM Employee
where concat(Id, Month) not in 
(select concat(Id, max(Month))
  from Employee 
  group by Id)
order by Id, Month desc

Rows 2 preceding:将当前行和它前面的两行划为一个窗口，因此sum函数就作 用在这三行上面

601.

SELECT s1.id, s1.visit_date, s1.people
FROM(
    SELECT 
    s.id, 
    s.visit_date, 
    s.people, 
    lead(people) OVER (ORDER BY id ASC) as next1,
    lead(people,2) OVER (ORDER BY id ASC ) as next2,
    lag(people) OVER (ORDER BY id ASC) as prev1,
    lag(people,2) OVER (ORDER BY id ASC ) as prev2
    FROM stadium as s
    ) AS s1    
WHERE (people>=100 and 
       ((next1>=100 and next2>=100)
        or (prev1>=100 and prev2>=100)
        or (prev1>=100 and next1>=100) -- <-this one is missing
       ));

615.

select department_salary.pay_month, department_id,
case
  when department_avg>company_avg then 'higher'
  when department_avg<company_avg then 'lower'
  else 'same'
end as comparison
from
(
  select department_id, avg(amount) as department_avg, date_format(pay_date, '%Y-%m') as pay_month
  from salary join employee on salary.employee_id = employee.employee_id
  group by department_id, pay_month
) as department_salary
join
(
  select avg(amount) as company_avg,  date_format(pay_date, '%Y-%m') as pay_month from salary group by date_format(pay_date, '%Y-%m')
) as company_salary
on department_salary.pay_month = company_salary.pay_month
;

618.

SELECT
        MAX(CASE WHEN continent = 'America' THEN name END )AS America,
        MAX(CASE WHEN continent = 'Asia' THEN name END )AS Asia,
        MAX(CASE WHEN continent = 'Europe' THEN name END )AS Europe  
FROM (SELECT *, ROW_NUMBER()OVER(PARTITION BY continent ORDER BY name) AS row_id FROM student) AS t
GROUP BY row_id