2024-11-24

Suhyeon Lee·2024년 11월 24일
자기주도학습
0

자기주도학습

목록 보기
56/83

CodeKata

SQL

115. Movie Rating

※ lexicographically order: 사전식 순서

  • 작성한 쿼리
(
SELECT
  name AS results
FROM
  Users u
  JOIN MovieRating r
  USING(user_id)
GROUP BY
  u.user_id
ORDER BY
  COUNT(movie_id) DESC
  , name ASC
LIMIT 1
)
UNION ALL
(
SELECT
  title AS results
FROM
  Movies m
  JOIN MovieRating r
  USING(movie_id)
WHERE
  DATE(created_at) BETWEEN '2020-02-01' AND '2020-02-29'
GROUP BY
  m.movie_id
ORDER BY
  AVG(rating) DESC
  , title ASC
LIMIT 1
)
;

→ WHERE절 조건을 DATE_FORMAT(created_at, '%Y%m') = '202002'로 해도 될듯(타자를 덜 칠 수 있다)

참고할 만한 다른 풀이

  • WINDOW FUNCITON, JOIN, GROUP BY
(
    SELECT 
       u.name AS results
    FROM (
        SELECT 
            mr.user_id,
            COUNT(*) AS totalRatings
        FROM MovieRating mr
        GROUP BY mr.user_id
        ORDER BY totalRatings DESC
    ) AS highest_reviewer
    JOIN users u ON highest_reviewer.user_id = u.user_id
    GROUP BY highest_reviewer.user_id 
    ORDER BY totalRatings DESC, u.name ASC 
    LIMIT 1
)
UNION
SELECT 
    avgResult.title AS results
FROM (
    SELECT 
        m.title,
        AVG(rating) OVER(PARTITION BY mr.movie_id) AS AvgRatingPerMovie
    FROM MovieRating mr
    JOIN movies m ON mr.movie_id = m.movie_id
    WHERE DATE_FORMAT(mr.created_at, '%Y-%m') = '2020-02'
    ORDER BY AvgRatingPerMovie DESC, m.title ASC
    LIMIT 1
) AS avgResult;
  • FIRST_VALUE
SELECT DISTINCT FIRST_VALUE(u.name) OVER(ORDER BY COUNT(r.movie_id) DESC, u.name ASC) AS results
FROM Users AS u LEFT JOIN MovieRating AS r
ON u.user_id=r.user_id
GROUP BY u.user_id
UNION ALL
SELECT DISTINCT FIRST_VALUE(m.title) OVER(ORDER BY AVG(r.rating) DESC, m.title ASC) AS results
FROM Movies AS m join MovieRating AS r
ON m.movie_id=r.movie_id
WHERE r.created_at BETWEEN '2020-02-01' AND '2020-02-29'
GROUP BY m.movie_id
  • EXTRACT
(SELECT name AS results
FROM MovieRating JOIN Users USING(user_id)
GROUP BY name
ORDER BY COUNT(*) DESC, name
LIMIT 1)

UNION ALL

(SELECT title AS results
FROM MovieRating JOIN Movies USING(movie_id)
WHERE EXTRACT(YEAR_MONTH FROM created_at) = 202002
GROUP BY title
ORDER BY AVG(rating) DESC, title
LIMIT 1);
  • CTE
WITH ques1 AS (
    SELECT rat.user_id, u.name, COUNT(rat.user_id) AS freq
    FROM MovieRating rat
    JOIN Users u on u.user_id = rat.user_id
    GROUP BY rat.user_id 
    ORDER BY freq DESC, u.name
    LIMIT 1
) ,

ques2 AS (
    SELECT rat.movie_id, m.title, AVG(rating) as avg_rating
    FROM MovieRating rat
    JOIN Movies m ON m.movie_id = rat.movie_id
    WHERE LEFT(created_at, 7) LIKE "2020-02"
    GROUP BY rat.movie_id
    ORDER BY avg_rating DESC, m.title
    LIMIT 1
)

(  -- part 1
SELECT name AS results
FROM ques1
)
UNION ALL
(  -- part 2
SELECT title AS results
FROM ques2
)
;

116. Restaurant Growth

  • 작성한 쿼리
WITH cte AS (
  SELECT
    DISTINCT visited_on
    , SUM(amount) OVER(
      ORDER BY visited_on
      RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW
    ) AS amount
    , MIN(visited_on) OVER() AS first_date
  FROM
    Customer
)
SELECT
  visited_on
  , amount
  , ROUND(amount/7, 2) AS average_amount
FROM
  cte
WHERE
  visited_on >= first_date+6
;

참고할 만한 다른 풀이

  • Runtime, Beats 1등한 사람 풀이
WITH CTE AS (
    SELECT visited_on,
    SUM(amount) AS daily_amount
    FROM Customer
    GROUP BY visited_on
)

SELECT visited_on,
    SUM(daily_amount) OVER (
        ORDER BY visited_on 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS amount,
    ROUND(AVG(daily_amount) OVER(
        ORDER BY visited_on 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 2) AS average_amount
FROM CTE

LIMIT 10000 OFFSET 6

→ limit: 결과 중 처음부터 몇 개만 가져오기

SELECT * FROM 테이블명 LIMIT 10; -- 처음 부터 10개만 출력하기 (1 ~ 10)

SELECT * FROM 테이블명 LIMIT 100, 10; -- 100번째부터 그 후 10개 출력하기 (101 ~ 110)

→ offset: 어디서부터 가져올지

SELECT * FROM 테이블명 ORDERS LIMIT 20 OFFSET 5; -- 5번째 행 부터 25행 까지 출력 (6 ~ 25)

-- limit 5, 20 과 같다고 보면 된다.
SELECT * FROM 테이블명 ORDERS LIMIT 5, 20

페이징(paging) 처리할 때 Mysql 에서는 Limit 과 offset 을 제공하여 훨씬 빠르게 원하는 위치에서 원하는 만큼의 데이터를 가져올 수 있다고 함

SELECT *
  FROM dbtable
  WHERE status = 'Y'
  ORDER BY CODE 
  LIMIT 20 OFFSET 0
  • Limit: 가져올 데이터 양
    offset: 가져올 데이터의 초기 위치값
    • 즉, 0(처음)에서부터 20건의 데이터를 가지고 오라는 의미
  • Paging을 하기 위해 0 부분을 변수로 지정하여, 페이지를 이동할 때마다 해당 페이지의 offset값을 계산하여 지정
  • WINDOW FUNCTION
SELECT 
    visited_on,
    SUM(SUM(amount)) OVER (
        ORDER BY visited_on 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS amount,
    ROUND(
        AVG(SUM(amount)) OVER (
            ORDER BY visited_on 
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ), 
        2
    ) AS average_amount
FROM customer
GROUP BY visited_on
LIMIT 18446744073709551615 OFFSET 6;

→ Time Complexity: O(n)
→ Space Complexity: O(m), where m is the number of unique visited_on values.

  • WINDOW FUNCTION (2)
select distinct visited_on,
        sum(amount) over w as amount,
        round((sum(amount) over w)/7, 2) as average_amount
    from customer
    WINDOW w AS ( 
            order by visited_on
            range between interval 6 day PRECEDING and current row
    )
    Limit 6, 999
  • Subquery
SELECT 
    c1.visited_on,
    (
        SELECT SUM(amount) 
        FROM customer c2
        WHERE c2.visited_on BETWEEN DATE_SUB(c1.visited_on, INTERVAL 6 DAY) AND c1.visited_on
    ) AS amount,
    ROUND(
        (
            SELECT SUM(amount) / COUNT(DISTINCT c2.visited_on)
            FROM customer c2
            WHERE c2.visited_on BETWEEN DATE_SUB(c1.visited_on, INTERVAL 6 DAY) AND c1.visited_on
        ), 
        2
    ) AS average_amount
FROM customer c1
GROUP BY c1.visited_on
LIMIT 18446744073709551615 OFFSET 6;

→ Time Complexity: O(n^2)
→ Space Complexity: O(m), where m is the number of unique visited_on values.

  • Subquery (2)
SELECT
    visited_on,
    (
        SELECT SUM(amount)
        FROM customer
        WHERE visited_on BETWEEN DATE_SUB(c.visited_on, INTERVAL 6 DAY) AND c.visited_on
    ) AS amount,
    ROUND(
        (
            SELECT SUM(amount) / 7
            FROM customer
            WHERE visited_on BETWEEN DATE_SUB(c.visited_on, INTERVAL 6 DAY) AND c.visited_on
        ),
        2
    ) AS average_amount
FROM customer c
WHERE visited_on >= (
        SELECT DATE_ADD(MIN(visited_on), INTERVAL 6 DAY)
        FROM customer
    )
GROUP BY visited_on;
  • CTE, Window function & CROSS JOIN
SELECT 
    x.visited_on,
    x.total AS amount,
    ROUND(x.total / 7, 2) AS average_amount
FROM (
    SELECT 
        c.*,
        SUM(amount) OVER (
            ORDER BY visited_on 
            RANGE INTERVAL 6 DAY PRECEDING
        ) AS total
    FROM customer c
) x, customer a
WHERE DATEDIFF(x.visited_on, a.visited_on) = 6
GROUP BY x.visited_on
ORDER BY x.visited_on;

→ Time Complexity: O(n^2)
→ Space Complexity: O(n^2)

  • CTE, WINDOW FUNCTION
with daily_spend as (
    select
        visited_on,
        sum(amount) as amount
    from
        Customer
    group by
        1
),
cte as (
    select
        visited_on,
        sum(amount) over (rows 6 preceding) as amount,
        round((sum(amount) over (rows 6 preceding))/7, 2) as average_amount
    from
        daily_spend
    group by
        1
)

select
    *
from
    cte
where
    datediff(visited_on, (select min(visited_on) from Customer)) >= 6

117. Friend Requests II: Who Has the Most Friends

  • 작성한 쿼리
WITH total AS(
  SELECT
    requester_id AS id
  FROM
    RequestAccepted
  UNION ALL
  SELECT
    accepter_id AS id
  FROM
    RequestAccepted
)
SELECT
  id
  , COUNT(*) AS num
FROM
  total
GROUP BY
  id
ORDER BY
  COUNT(*) DESC
LIMIT 1
;

참고할 만한 다른 풀이

WITH TEMPTABLE AS (
    SELECT requester_id AS id FROM RequestAccepted
    UNION ALL
    SELECT accepter_id AS id FROM RequestAccepted
),
workingTABLE AS (
    SELECT id, id AS friends FROM TEMPTABLE
)
SELECT id, COUNT(friends) AS num
FROM workingTABLE
GROUP BY id
ORDER BY num DESC LIMIT 1;
WITH TYPE1 AS (
    SELECT REQUESTER_ID, COUNT(ACCEPTER_ID) AS ASKFRIENDS FROM REQUESTACCEPTED 
GROUP BY REQUESTER_ID
),

TYPE2 AS (
    SELECT ACCEPTER_ID, COUNT(REQUESTER_ID) AS FRIENDS FROM REQUESTACCEPTED 
GROUP BY ACCEPTER_ID
)

(SELECT REQUESTER_ID AS ID, COALESCE(ASKFRIENDS,0) + COALESCE(FRIENDS,0) AS NUM FROM TYPE1
LEFT JOIN TYPE2 ON TYPE2.ACCEPTER_ID = TYPE1.REQUESTER_ID
)
UNION
(SELECT ACCEPTER_ID AS ID, COALESCE(ASKFRIENDS,0) + COALESCE(FRIENDS,0) AS NUM FROM TYPE2
LEFT JOIN TYPE1 ON TYPE2.ACCEPTER_ID = TYPE1.REQUESTER_ID
)
ORDER BY NUM DESC
LIMIT 1;
WITH FriendsCount AS (
    SELECT 
        requester_id AS user_id
    FROM RequestAccepted
    UNION ALL
    SELECT 
        accepter_id AS user_id
    FROM RequestAccepted
),
FriendTotals AS (
    SELECT 
        user_id,
        COUNT(*) AS total_friends
    FROM FriendsCount
    GROUP BY user_id
),
MaxFriends AS (
    SELECT 
        MAX(total_friends) AS max_friends
    FROM FriendTotals
)
SELECT 
    ft.user_id as id,
    ft.total_friends AS num
FROM FriendTotals ft
JOIN MaxFriends mf
ON ft.total_friends = mf.max_friends;

118. Investments in 2016

  • 작성한 쿼리
WITH same_2015 AS(
  SELECT
    tiv_2015
  FROM
    Insurance
  GROUP BY
    tiv_2015
  HAVING
    COUNT(*) > 1
)
, not_same_city AS(
  SELECT
    pid
    , tiv_2015
    ,tiv_2016
  FROM
    Insurance
  GROUP BY
    lat
    , lon
  HAVING
    COUNT(*) = 1
)
SELECT
  ROUND(SUM(tiv_2016), 2) AS tiv_2016
FROM
  not_same_city
WHERE
  tiv_2015 IN (TABLE same_2015)
;

참고할 만한 다른 풀이

Select Round(Sum(tiv_2016),2) as tiv_2016 from Insurance 
where tiv_2015 in (
    Select tiv_2015 from insurance group by tiv_2015 Having count(*)>1
)
and (lat,lon) in (
    Select lat, lon from insurance group by lat,lon having count(*)=1
)
WITH
  InsuranceWithCounts AS (
    SELECT
      tiv_2016,
      COUNT(*) OVER(PARTITION by tiv_2015) AS tiv_2015_count,
      COUNT(*) OVER(PARTITION by lat, lon) AS city_count
    FROM Insurance
  )
SELECT ROUND(SUM(tiv_2016), 2) AS tiv_2016
FROM InsuranceWithCounts
WHERE tiv_2015_count > 1
  AND city_count = 1;
WITH UniqueLocations AS (
    SELECT lat, lon
    FROM Insurance
    GROUP BY lat, lon
    HAVING COUNT(*) = 1
),
DuplicateTIV2015 AS (
    SELECT tiv_2015
    FROM Insurance
    GROUP BY tiv_2015
    HAVING COUNT(*) > 1
)
SELECT ROUND(SUM(tiv_2016), 2) AS tiv_2016
FROM Insurance
WHERE (lat, lon) IN (SELECT lat, lon FROM UniqueLocations)
  AND tiv_2015 IN (SELECT tiv_2015 FROM DuplicateTIV2015);
SELECT ROUND(SUM(tiv_2016),2) as tiv_2016 FROM Insurance
WHERE 
pid NOT IN
(SELECT a.pid FROM Insurance a
INNER JOIN Insurance b on (a.lat,a.lon) = (b. lat,b.lon)
WHERE a.pid <> b.pid)
AND
tiv_2015 IN
(SELECT tiv_2015 FROM Insurance
GROUP BY tiv_2015 HAVING COUNT(pid)>1);

Python

49. 두 개 뽑아서 더하기

def solution(numbers):
    answer = []
    num_set = set()
    for i in range(len(numbers)):
        for j in range(i+1, len(numbers)):
            num_set.add(numbers[i]+numbers[j])
    answer = list(sorted(num_set))
    return answer

→ combination 문제니까 파이썬에 관련 함수가 있을 것 같아 찾아보니 from itertools import combinations이 있었음

from itertools import combinations
def solution(numbers):
    return list(sorted(set([sum([i,j]) for i, j in combinations(numbers,2)])))

참고할 만한 다른 풀이

from itertools import combinations
def solution(numbers):
    answer = set()
    for i in list(combinations(numbers,2)):
        answer.add(sum(i))
    return sorted(answer)
  • 파이썬 기본 라이브러리인 itertools의 combinations 라는 내장함수를 사용하여 인자값에 따라 해당 요소로 구할수 있는 모든 조합을 리턴
    (예) combinations(numbers, 2)는 numbers 리스트 안에 2개의 요소로 구할 수 있는 모든 조합을 반환
    • itertools 라이브러리의 permutations(리스트의 순열을 리턴하는 함수), product(두개 이상의 리스트에서 조합을 리턴하는 함수)도 경우에 따라 유용하게 사용할 수 있음
from itertools import combinations
def solution(numbers):
    return sorted(set(sum(i) for i in list(combinations(numbers, 2))))
def solution(numbers): return sorted({numbers[i] + numbers[j] for i in range(len(numbers)) for j in range(len(numbers)) if i>j})
def solution(numbers):
    answer = []

    for idx, i in enumerate(numbers[:-1]):
        for j in numbers[idx + 1:]:
            answer.append(i + j)

    return sorted(set(answer))
def solution(numbers):
    answer = []
    i = 0
    while i < len(numbers):
        for j in range(i + 1, len(numbers)):
            summation = numbers[i] + numbers[j]
            if not summation in answer:
                answer.append(summation)
        i += 1
    answer.sort()
    return answer

SDL

추가 학습

회고

  • UniGetUI 추천한다는데 왜인지 한번 읽어보기
profile
Suhyeon Lee
2 B R 0 2 B
이전 포스트

SQL Window Function 심화

다음 포스트

[251124] 추가 공부

0개의 댓글

관련 채용 정보