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

소프·2022년 3월 9일
0

SQL Study

목록 보기
3/6
post-thumbnail
  1. 일별 블로그 방문자 수 집계

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

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
  1. 일일 매출액 구하기

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

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
  1. 성적 순위 구하기

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

SELECT score
     , dense_rank() OVER (ORDER BY score DESC) as 'rank'
FROM Scores
  1. 부서별 높은 급여받는 사람 찾기

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

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);
  1. 여행 취소율 구하기

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

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'
profile
세상을 긍정적으로 변화시키는 Business Analyst가 되기위해 노력중입니다.

0개의 댓글