SQL Tricks - joins & LeetCode

SQL Tricks - joins & LeetCode.sql

Self-Join:

1. https://leetcode.com/problems/rising-temperature/
ANS:

SELECT w1.id
FROM Weather w1
JOIN Weather w2
ON DATEDIFF(w1.recordDate, w2.recordDate) = 1
WHERE w1.temperature > w2.temperature;

2. https://leetcode.com/problems/average-selling-price/description/
Ans:

Cross Join (Cartassian Join):
> combination of every row of the 1st table with every row of the 2nd table i.e. mxn
> No need of `ON` clause
> https://youtu.be/RehbnzKHS28?t=621

Cross join + Group By on multi column (nested groups)
Eg:
> GROUP BY student_id, subject_name 
the squashing will happen at last column i.e. `subject_name`
Q: https://leetcode.com/problems/students-and-examinations/description/
Ans:

WITH CTE AS (
    SELECT *, COUNT(subject_name) as freq
    FROM Examinations
    GROUP BY student_id, subject_name
)


# Write your MySQL query statement below
SELECT S.student_id, S.student_name, T.subject_name, IFNULL(CTE.freq, 0) AS attended_exams 
FROM Students S
CROSS JOIN Subjects T 

LEFT JOIN CTE
ON CTE.student_id = S.student_id and CTE.subject_name = T.subject_name

ORDER BY S.student_id, T.subject_name ASC;

4. Good Q:
https://leetcode.com/problems/percentage-of-users-attended-a-contest/editorial/


5. More on Join:
https://leetcode.com/problems/average-selling-price/

6.
https://leetcode.com/problems/movie-rating/description/

7. To calculate the number of users and the number of blogs they made in the month of February 2020
WITH CTE AS (
    SELECT *, COUNT(*) as count_blogs
    FROM `blog`
    WHERE YEAR(order_date) IN (2020, 2019) AND MONTH(order_date) = 2
    GROUP BY author_id
)

SELECT sub.count_blogs, count(u.id) as user_count
FROM user as u
LEFT JOIN CTE as sub
ON sub.author_id = u.id
GROUP BY sub.count_blogs;


8. [INTERVIEW] Write query to show the row which are duplicate for values c1 and c2 + show the other values
WITH CTE1 as (
    SELECT *, COUNT(*) OVER(PARTITION BY c1, c2) as `repeating`
    FROM `tests`
)

// grouping will merge those repeating columns
SELECT *
FROM CTE1
GROUP BY c1, c2;

q. https://leetcode.com/problems/market-analysis-i/
select
    distinct t.user_id as buyer_id,
    t.join_date,
    count(order_id) over(partition by buyer_id) as `orders_in_2019`
FROM (
    select * from Users u
    left join Orders o
    on o.buyer_id=u.user_id and year(o.order_date)=2019
) as t


.....................................................................................
Stored Procedures

-- `DELIMITER` enforce double slash as full stop instead of semi colon
DELIMITER //

CREATE PROCEDURE `get_data_by_address` (
	address VARCHAR(100)   -- for some cases use @address VARCHAR(100)
)
BEGIN
  SELECT * FROM Persons
  WHERE address = address;
END //

CALL get_data_by_address('KLJ') //

..................................................................................

START TRANSACTION;
INSERT INTO employees (name, age) VALUES ('John Doe', 30);
UPDATE salaries SET salary = 50000 WHERE employee_id = 1;
COMMIT;