주간 SQL Study 3차시 (SolveSQL, LeetCode 문제풀이)

1️⃣ 일별 블로그 방문자 수 집계

Q1. https://solvesql.com/problems/blog-counter/

solvesql

 

SELECT event_date_kst AS dt, count(distinct user_pseudo_id) AS users
FROM ga
WHERE event_date_kst BETWEEN '2021-08-02' AND '2021-08-09'
GROUP BY event_date_kst
-- HAVING event_date_kst>='2021-08-02' AND event_date_kst <= '2021-08-09'
ORDER BY dt ASC

 

2️⃣ 일일 매출액 구하기

Q2. https://solvesql.com/problems/olist-daily-revenue/

solvesql

 

SELECT event_date_kst AS dt, count(distinct user_pseudo_id) AS users
FROM ga
WHERE event_date_kst BETWEEN '2021-08-02' AND '2021-08-09'
GROUP BY event_date_kst
-- HAVING event_date_kst>='2021-08-02' AND event_date_kst <= '2021-08-09'
ORDER BY dt ASC

 

3️⃣ 성적 순위 구하기

Q3. https://leetcode.com/problems/rank-scores/

Rank Scores - LeetCode

 

SELECT score
     , dense_rank() OVER (ORDER BY score DESC) as 'rank'
FROM Scores

4️⃣ 부서별 높은 급여받는 사람 찾기

Q4. https://leetcode.com/problems/department-highest-salary/

Department Highest Salary - LeetCode

SELECT d.name as Department
     , e.name as Employee
     , e.salary as Salary
FROM Employee e
INNER JOIN Department d on e.departmentId = d.id
WHERE e.salary=(SELECT max(salary)
                FROM Employee e1
                GROUP BY departmentId
                HAVING departmentId=e.departmentId);

 

5️⃣ 여행 취소율 구하기

Q5. https://leetcode.com/problems/trips-and-users/

solvesql

SELECT d.name as Department
     , e.name as Employee
     , e.salary as Salary
FROM Employee e
INNER JOIN Department d on e.departmentId = d.id
WHERE e.salary=(SELECT max(salary)
                FROM Employee e1
                GROUP BY departmentId
                HAVING departmentId=e.departmentId);
                
--
WITH not_banned AS(
SELECT *
FROM trips
WHERE client_id NOT IN (SELECT users_id
                        FROM users 
                        WHERE banned = "Yes")
      AND driver_id NOT IN (SELECT users_id
                            from users 
                            WHERE banned = "Yes")
)
SELECT request_at AS DAY
     , ROUND(SUM(CASE WHEN status != 'completed' THEN 1 ELSE 0 END) / COUNT(*),2) AS 'Cancellation Rate'
FROM not_banned
GROUP BY request_at
WHERE request_at BETWEEN '2013-10-01' AND '2013-10-03'