코드를 입력하세요

WITH cte AS (
    select
        user_id,
        user_name,
        count(user_id) over() as total_num
    from Users
)
    select 
        distinct r.contest_id,
        round(count(r.user_id) over(partition by r.contest_id) / c.total_num *100.0, 2) as percentage
    from Register r
    join cte c on r.user_id = c.user_id
    order by 2 desc, 1

아래는 다른 사람이 작성한 더 효율이 높은 쿼리입니다.

코드를 입력하세요

SELECT r.contest_id, round(count(*) / (SELECT count(*) from Users) * 100 ,2) as percentage
FROM REGISTER r
LEFT JOIN Users u
ON r.user_id = u.user_id
GROUP BY r.contest_id
ORDER BY percentage DESC, r.contest_id ASC

위 사람은 select 문에 서브 select문을 작성하여 풀었습니다. 여기서 유의해야 할 점은 join에 r.user_id = u.user_id를 해주어야 한다는 점입니다.

그리고 윈도우 함수 안에는 distinct문을 함께 못쓴다는 사실 또한 새로 배운점이었습니다.

코드를 입력하세요
with cte as (
SELECT 
    p.product_id,
    p.start_date,
    p.end_date,
    p.price,
    s.product_id AS units_product_id,
    s.purchase_date,
    COALESCE(s.units, 0) AS units
FROM 
    Prices p 
LEFT JOIN 
    UnitsSold s 
ON 
    p.product_id = s.product_id 
    AND s.purchase_date BETWEEN p.start_date AND p.end_date
), 
cte2 as (
select product_id,
       round(sum(price * units) / sum(units), 2) as average_price
from cte
group by 1
)
select product_id, 
       coalesce(average_price, 0) as average_price
from cte2

이 문제를 풀며 새로 알게된 사실들이 있다.

1. WITH문의 SELECT절을 쓸 때 같은 이름이 있다면 다른 이름으로 바꾸어주자!

2. COALESCE 함수는 SELECT, WHERE절에서 쓰인다. 처음에는 JOIN문에다가 썼었는데 작동되지 않았다.

COALESCE 문법은 COALESCE(A,N) 인데, A컬럼에서 NULL이 아닌 것은 A값 그대로 가져오고, NULL인 값은 N으로 치환한다. COALESCE(A,B,C,N) 이라면 A가 NULL 값이면 B를 가지고 오고, 이때 B또한 NULL이면 C값을 가지고 오고, 만약 C도 NULL이라면 N을 가져오는 식이다.

아래처럼 풀이한 다른 사람도 있었다.

코드를 입력하세요
SELECT p.product_id, IFNULL(ROUND(SUM(units*price)/SUM(units),2),0) AS average_price
FROM Prices p LEFT JOIN UnitsSold u
ON p.product_id = u.product_id AND
u.purchase_date BETWEEN start_date AND end_date
group by product_id

IFNULL이라는 함수를 사용했는데 IF랑 똑같은 문법을 가지고 있었다.

IFNULL(A,B) : A가 NULL이 아니면 그대로 표현해주고, NULL이라면 B값으로 대체

IFNULL과 COALESCE 의 가장 큰 차이점이 있다면 IFNULL은 인자를 2개밖에 가져올 수 없지만 COALESCE는 여러가지의 인자를 가져올 수 있다는 차이가 있다.

아래 간단하게 EDA를 어떻게 진행하는지 시각화 방법과 함께 설명드리겠습니다.

1. .describe (데이터 확인하기)
2. counterplot : x축은 범주형, y축은 해당 범주의 count
3. barplot: x축은 범주형, y축은 연속형 값 (기본값으로 평균값이 나옴)
4. boxplot: x축은 범주형, y축은 연속형 값
5. histplot: x축 연속형 값, y축은 count (bins = ?? 를 통해 막대 범위를 정할 수 있음)
6. scatterplot: x, y 모두 수치형 변수
7. pairplot: 수치형의 모든 데이터를 가지고 그래프를 그려줌.

F검정 p-value

의미: F검정은 회귀 모델 전체가 유의미한지를 테스트합니다. 즉, 모델이 무작위로 예측하는 것보다 더 나은 예측을 하는지를 검정합니다.

해석: p-value가 매우 작다면 (일반적으로 0.05 이하), 회귀 모델 전체가 통계적으로 유의미하다는 것을 의미합니다. 즉, 최소한 하나의 독립변수가 종속변수에 영향을 미친다는 증거가 됩니다.

사용 상황: 전체 모델의 유의미성을 판단할 때 사용합니다.

중요성: F검정 p-value는 회귀 모델 전체의 유의미성을 평가합니다. 즉, 모델이 종속변수를 설명하는 데 있어서 의미 있는지를 먼저 확인합니다.

우선 순위 이유: 모델이 전체적으로 유의미하지 않다면, 개별 독립변수의 유의미성이나 모델의 설명력을 논의하는 것이 의미가 없습니다. 따라서, F검정 p-value가 가장 먼저 고려됩니다.

T검정 p-value

의미: T검정은 각 독립변수가 종속변수에 대해 유의미한 영향을 미치는지를 개별적으로 테스트합니다.

해석: 각 독립변수에 대해 p-value가 작다면 (일반적으로 0.05 이하), 해당 변수는 종속변수에 통계적으로 유의미한 영향을 미친다는 것을 의미합니다.

사용 상황: 각 독립변수의 유의미성을 판단할 때 사용합니다. 각 변수의 중요도를 평가합니다.

결정계수 (R-squared)

의미: R-squared 는 독립변수가 종속변수의 변동을 얼마나 설명하는지를 나타내는 지표입니다. 0에서 1 사이의 값을 가지며, 1에 가까울수록 모델이 데이터를 잘 설명한다는 의미입니다.

해석: 예를 들어,R-squared =0.8이면 독립변수가 종속변수의 변동 80%를 설명한다는 의미입니다.

한계: 높은 R-squared 값이 항상 좋은 모델을 의미하는 것은 아닙니다. 과적합(overfitting)일 가능성도 있습니다. R-squared는 모델의 설명력을 나타내지만, 모델이 유의미한지를 먼저 확인한 후에 고려해야 합니다.

모델의 설명력을 나타내는 R-squared와 모델의 유의미성을 나타내는 F검정의 차이를 더 알고파서 물어보았다.

피봇테이블을 만들기 위해서는 크게 두가지 스텝이 필요하다.

1. 본인이 기준하고 싶은 컬럼 2개를 선정하고 어떤 값을 넣고 싶은지 집계함수를 넣는다.
2. 1번에서 만든 테이블을 가지고 max(if()) 함수를 통해 피봇테이블을 완성한다.

unpivot

언피봇은 피봇의 반대 개념이다. 아래의 데이터를 unpivot 하는 과정을 보면 이해하기가 쉽다. 그럼 아래와 같은 결과가 나옵니다.

(출처: 스파르타코딩클럽, 엑셀보다 쉽고 빠른 SQL 강의자료, SQL 챌린지 세션 강의자료)

나의 풀이

코드를 입력하세요
with m6 as(
SELECT user_id,
       TO_CHAR(event_date, 'MM') as month
FROM user_actions
where TO_CHAR(event_date, 'MM') = '06'
),
m7 as (
SELECT user_id,
       TO_CHAR(event_date, 'MM') as month
FROM user_actions
where TO_CHAR(event_date, 'MM') = '07'
)
select cast(m7.month as integer) as month,
       count(DISTINCT m6.user_id)
from m6 join m7 on m6.user_id = m7.user_id
group by cast(m7.month as integer)

여기서 내가 새로 알게 된점 1: 나는 MYSQL을 주로 공부하였고, POSTGRE는 잘 모른다. DATE_FORMAT 함수를 POSTGRE에서는 _TO_CHAR_함수로 쓴다.

여기서 내가 새로 알게 된점 2: 문자열을 숫자로 바꿀 때 MYSQL: CAST(m7.month as INT) 또는 CONVERT(m7.month as INT) POSTGRE: CAST(m7.month as INTEGER) 또눈 CAST(m7.month :: INTEGER)

다른 사람 풀이

코드를 입력하세요
with cte as (
SELECT User_id,event_type, event_date, EXTRACT(MONTH FROM event_Date) as mth
from user_actions
where event_date BETWEEN '06/01/2022' and '07/31/2022'
),
cte2 as (
Select * , lag(mth) over(PARTITION BY user_id order by event_date) as lst_mth
from cte)

select mth,
       count(DISTINCT user_id)
from cte2
where mth - lst_mth = 1
group by mth

여기서 내가 알게된점: EXTRACT (MONTH FROM event_date) 를 하면 event_date에서 MONTH만 추출해 숫자 타입으로 반환함.

인상 깊었던 점: lag 함수를 사용한게 인상이 깊었다.

우선 이 테이블(sales_transaction_v)에서 DATE컬럼이 날짜 타입이 아니고 문자 타입이었어서 그 타입을 바꾸고 시작하고 싶었다.

*테이블 컬럼의 타입을 바꾸고 싶다면 크게 두가지 방법이 있다. *

1) 테이블에 새로운 컬럼을 추가하고 그 컬럼을 새롭게 지정해준다. (현재 이 테이블에서 DATE컬럼은 문자열로 12/9/2019 이런식으로 되어있었다.)

2) 테이블 해당 컬럼의 타입만 바꾼다. (문자열을 날짜 타입으로 바꿀때 MYSQL에서는 STR_TO_DATE 라는 함수를 쓴다.)

그리하여 DBEAVER에 아래와 같이 했더니 실행이 안되었다... alter table sales_transaction_v add column new_date DATE update sales_transaction_v set new_date = date_format(str_to_date(Date, '%m/%d/%Y'),'%Y-%m-%d')

여기서 나는 'ALTER 문법에는 ; 을 끝에 붙여야하는구나' 와 'DATE_FORMAT 을 쓰면 문자열로 반환되는구나' 를 새롭게 알게되었다.

또한 DATEDIFF 로는 MYSQL에서 일(DAY)차이만 알 수 있고 월 단위 차이는 알 수 없었다. 월 차이를 알고 싶다면 직접 아래처럼 계산해야했다.

나는 깔끔하게 DATE를 '%Y-%m' 형태로 바꾸어 월 차이를 구하고 싶었는데 그렇게 하였을 때 STR_TO_DATE 함수가 쓰여지지 못하는 점을 알게 되었다. 그래서 나는 모든 DATE 컬럼 값을 '%Y-%m-%01' 값으로 DATE_FORMAT을 시킨 뒤 다시 결과값(문자)을 STR_TO_DATE를 통해 날짜 타입으로 바꾸어주었다.

그리하여 결국 아래와 같은 코드로 이 문제를 풀었다.

alter table  sales_transaction_v add column new_date DATE;

DESCRIBE sales_transaction_v;

UPDATE sales_transaction_v 
SET new_date = STR_TO_DATE(`Date`, '%m/%d/%Y');

SELECT *
FROM sales_transaction_v
order by new_date;

with date_a as (
select customerno,
       min(new_date) over(partition by customerno order by new_date) as first_order_month,
       date_format(new_date, '%Y-%m-%01') as order_month
from sales_transaction_v
), date_b as (
select customerno,
       date_format(first_order_month, '%Y-%m-%01') as first_order_month,
       str_to_date(order_month, '%Y-%m-%d') as order_month
from date_a
), date_c as (
select customerno,
       str_to_date(first_order_month,'%Y-%m-%d') as first_order_month,
       order_month

from date_b
), date_d as (
select customerno,
       first_order_month,
       order_month,
       (YEAR(order_month) - YEAR(first_order_month)) * 12 + MONTH(order_month) - MONTH(first_order_month) as month_diff
from date_c
)
select first_order_month,
       count(distinct case when month_diff = 0 then customerno end) as month0,
       count(distinct case when month_diff = 1 then customerno end) as month1,
       count(distinct case when month_diff = 2 then customerno end) as month2,
       count(distinct case when month_diff = 3 then customerno end) as month3,
       count(distinct case when month_diff = 4 then customerno end) as month4,
       count(distinct case when month_diff = 5 then customerno end) as month5,
       count(distinct case when month_diff = 6 then customerno end) as month6,
       count(distinct case when month_diff = 7 then customerno end) as month7,
       count(distinct case when month_diff = 8 then customerno end) as month8,
       count(distinct case when month_diff = 9 then customerno end) as month9,
       count(distinct case when month_diff = 10 then customerno end) as month10,
       count(distinct case when month_diff = 11 then customerno end) as month11,
       count(distinct case when month_diff = 12 then customerno end) as month12
from date_d
where customerno is not null
group by first_order_month

이 한 문제를 푸느라 정말 많은 시간을 들였다.. 중간중간 계속 막힐때마다 정말 짜증났지만 마지막에 딱 문제를 풀었을 때 정말 큰 쾌감이 따라왔다😁

아래는 튜터님이 직접 푸신 답안지이다.

WITH first AS (
    SELECT customer_id
          ,order_id
          ,order_date
          ,MIN(order_date) OVER(PARTITION BY customer_id ORDER BY order_date) AS first_order_date
    FROM orders   
    WHERE customer_id IS NOT NULL 

    ),mon AS (

    SELECT customer_id
          ,order_id
          ,order_date      
          ,DATE_FORMAT(order_date,'%Y-%m-%01')as order_month
          ,DATE_FORMAT(first_order_date,'%Y-%m-%01') as first_order_month
    FROM first 

    )

SELECT first_order_month
      ,COUNT(DISTINCT customer_id) as month0
      ,COUNT(DISTINCT CASE WHEN DATE_ADD(first_order_month,INTERVAL 1 month)=order_month THEN customer_id ELSE NULL END)month1
      ,COUNT(DISTINCT CASE WHEN DATE_ADD(first_order_month,INTERVAL 2 month)= order_month THEN customer_id ELSE NULL END)month2
      ,COUNT(DISTINCT CASE WHEN DATE_ADD(first_order_month,INTERVAL 3 month)= order_month THEN customer_id ELSE NULL END)month3
      ,COUNT(DISTINCT CASE WHEN DATE_ADD(first_order_month,INTERVAL 4 month)= order_month THEN customer_id ELSE NULL END)month4
      ,COUNT(DISTINCT CASE WHEN DATE_ADD(first_order_month,INTERVAL 5 month)= order_month THEN customer_id ELSE NULL END)month5
      ,COUNT(DISTINCT CASE WHEN DATE_ADD(first_order_month,INTERVAL 6 month)= order_month THEN customer_id ELSE NULL END)month6
      ,COUNT(DISTINCT CASE WHEN DATE_ADD(first_order_month,INTERVAL 7 month)= order_month THEN customer_id ELSE NULL END)month7
      ,COUNT(DISTINCT CASE WHEN DATE_ADD(first_order_month,INTERVAL 8 month)= order_month THEN customer_id ELSE NULL END)month8
      ,COUNT(DISTINCT CASE WHEN DATE_ADD(first_order_month,INTERVAL 9 month)= order_month THEN customer_id ELSE NULL END)month9
      ,COUNT(DISTINCT CASE WHEN DATE_ADD(first_order_month,INTERVAL 10 month)= order_month THEN customer_id ELSE NULL END)month10
      ,COUNT(DISTINCT CASE WHEN DATE_ADD(first_order_month,INTERVAL 11 month)= order_month THEN customer_id END)month11
FROM mon
GROUP BY first_order_month
ORDER BY first_order_month

위 답안지는 SQL환경이 달라 약간 다르지만 나와 크게 다른점은 DATE_ADD 함수를 쓰신 부분이다. MYSQL 환경에서 DATE_ADD를 쓰는 문법은 위에 참고해주시길 바란다. 반대로 DATE를 빼고 싶다면 DATE_SUB 함수를 쓰면되고 문법은 DATE_ADD와 같다.

확률 변수: 무작위 실험을 했을 때 나타나는 결과를 수치 값으로 표현한 변수

ex) 동전 던지기 실험인 경우:

1) 앞면일 경우 수치 값 1, 2) 뒷면일 경우 수치 값 0 을 임의로 정했다고 하면 이때는 0과 1이 확률 변수가 됨.

확률분포: 확률변수의 모든 값과 그에 대응하는 확률이 어떻게 분포하고 있는지를 말합니다.

*확률함수: 확률변수에 의해 정의된 실수를 확률(0~1사이)에 대응시키는 함수를 뜻합니다. *

ex) 동전을 2번 던져 나올 수 있는 확률 변수 {0,1,2} 에 대하여 각각 0일 때 (뒷면만 2번) 확률이 1/4, 1일 때 1/2, 2일 때 1/4 를 대응 시키는 함수

확률 변수와 확률 함수가 통계에 필요한 이유는 우리가 특정 확률변수의 확률함수를 알고 있다면 사건이 일어날 확률을 예측(계산) 할 수 있기 때문입니다

출처: https://drhongdatanote.tistory.com/49

내가 처음 적었던 쿼리는 아래와 같았다.

WITH create_id as (
SELECT *
FROM tutorial.yammer_events 
WHERE event_name = 'create_user'),
sign_up as (
SELECT *
FROM tutorial.yammer_events 
WHERE event_name = 'complete_signup')

SELECT t.location,
       t.device,
       ROUND(COUNT(CASE WHEN DATE_PART('hour',  s.occurred_at::timestamp -  c.occurred_at::timestamp) < 24 THEN 1 END) * 1.0 /
       COUNT(*) * 100, 2)
FROM tutorial.yammer_events t 
LEFT JOIN create_id c on t.user_id = c.user_id
JOIN sign_up s on c.user_id = s.user_id
GROUP BY t.location, t.device

이 쿼리에는 많은 문제가 있다😂 답은 모든 행에서 100%를 반환하였는데 그 이유는 여러가지가 있겠지만 주요한 이유중에 하나가 join의 활용 이해부족이다.

나는 A,B,C 테이블을 각각 A와 B를 LEFT JOIN으로 묶고, B와 C를 INNER JOIN으로 묶으면, A의 전체 행과 B,C의 교집합이 합쳐지는줄 알았다.

하지만 내가 완전 잘못 이해하고 있었다. 당연히 있어야할 B와 C 테이블에 NULL값이 보이지 않았다.. 그 이유는 아래와 같았다.

두번째 조인을 할때에 B와 C의 공통인 행을 불러오며 *A테이블에서 또한 B와 C의 공통인 행들만 가져온다. *

결국 마지막 순서의 JOIN이 중요하다는 생각을 하게 되었다.

정답 쿼리를 아래 작성하며 마친다.

WITH create_id as (
SELECT *
FROM tutorial.yammer_events 
WHERE event_name = 'create_user'),
sign_up as (
SELECT *
FROM tutorial.yammer_events 
WHERE event_name = 'complete_signup')

SELECT c.location,
       c.device,
       ROUND(COUNT(DISTINCT s.user_id) * 1.0 / COUNT(DISTINCT c.user_id) * 100, 2) as conversion_percentage 
FROM create_id c
LEFT JOIN sign_up s on c.user_id = s.user_id 
and c.occurred_at <= s.occurred_at
and DATE_PART('hour', s.occurred_at ::timestamp - c.occurred_at::timestamp) <= 24
GROUP BY 1,2

1.TikTok https://datalemur.com/questions/signup-confirmation-rate 위의 링크를 통해 문제를 풀어볼 수 있다.

내용을 요약하자면 틱톡을 가입하면 문자로 가입 확정 문자가 가는데 이를 수락해야 진짜 가입이 완료가 되어 계정이 활성화 된다고 한다. 여기서 계정이 얼마나 활성화가 되었는지 활성화 비율을 구하는 문제이다.

#정답 코드
SELECT
ROUND(count (DISTINCT CASE WHEN signup_action = 'Confirmed' THEN user_id END) * 1.0 / count(DISTINCT user_id),2)
FROM emails e LEFT JOIN texts t on e.email_id = t.email_id 

여기서 내가 몰랐던 점은 case 문을 count 와 함께 쓸 수 있다는 점이었다. 나는 case문의 쓰임에 대하여 gpt에다가 물어보았다.

크게 아래와 같이 2가지 쓰임세로 쓸 수 있었는데

1. 첫번째로는 조건에 따른 값을 반환할 때 쓰인다.

SELECT 
    employee_id,
    name,
    CASE 
        WHEN department = 'Sales' AND salary > 50000 THEN 'Senior Sales'
        WHEN department = 'Sales' AND salary <= 50000 THEN 'Junior Sales'
        WHEN department = 'IT' AND experience > 5 THEN 'Senior IT'
        ELSE 'Other'
    END AS position
FROM 
    employees;

이러한 경우가 내가 원래 알고 있던 쓰임이었다.

2. 두번째로 조건에 따른 집계를 할 때 쓰일 수 있다.

SELECT 
    region,
    COUNT(CASE WHEN sales > 1000 THEN 1 END) AS high_sales,
    COUNT(CASE WHEN sales <= 1000 THEN 1 END) AS low_sales
FROM 
    sales_data
GROUP BY 
    region;

위와 같은 경우는 이 문제와 같은 경우이다. count나 sum과 함께 select절에 쓰일 수 있다는 것을 새로 알게되었다.

또 새롭게 알게 된 점은 정수 나누기 정수를 하였을 때 값이 0이 반환될 때도 있다는 것이다. 그리하여 정수 / 정수 를 할 때는 둘중 하나에 대하여 실수로 바꾸어 주어야한다. 실수로 바꾸어주기 위해서는 크게 두가지 방법이 있다고 한다.

SELECT 
  CAST((SELECT COUNT(DISTINCT email_id) FROM texts WHERE signup_action = 'Confirmed') AS DECIMAL) /
  CAST((SELECT COUNT(DISTINCT email_id) FROM emails) AS DECIMAL) AS confirmed_ratio

위처럼 as DECIMAL을 쓰는 방법과 1.0을 곱해주는 방법이 있다.

2.Twitter https://datalemur.com/questions/rolling-average-tweets

위 문제는 각 트위터 유저에 대한 트위터 사용 횟수의 3일 이동 평균을 구하는 것이다.

*이동 평균이란? *

SELECT user_id,
       tweet_date,
       ROUND(AVG(tweet_count) OVER(PARTITION BY user_id ORDER BY user_id,tweet_date ROWS BETWEEN 2 preceding and current row),2) as rolling_avg_3d
FROM tweets
GROUP BY tweet_date, user_id,tweet_count

user_id로 나누어 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW 를 통해 앞에 2개의 행의 tweer_count 값과 현재의 값을 가지고 평균을 낸다. 첨에 partition by를 빼먹어 많이 헤맸었다..

3.Verizon https://datalemur.com/questions/international-call-percentage

이 문제도 첫번째 TikTok문제와 유사하다. 해외 통화를 한 퍼센티지를 구하는 문제이다.

WITH receiver as (
SELECT caller_id as receiver_id,
       country_id as receiver_country_id
FROM phone_info
)

SELECT ROUND(COUNT(CASE WHEN i.country_id != receiver_country_id THEN 1 END )*1.0 / COUNT(*) *100,1) as international_calls_pct
FROM phone_calls c 
JOIN phone_info i on c.caller_id = i.caller_id 
JOIN receiver r on c.receiver_id = r.receiver_id

나는 이렇게 풀어 정답을 맞추었지만 구지 with절을 쓰지 않아도 되었다. 나는 JOIN을 할 때에 이상하게 컬럼명이 똑같아야 되는줄 알았다😂 그래서 WITH절을 구지 써서 컬럼명을 맞추었다.

그런데 아래처럼 JOIN을 활용할 수 있어 새로 알게되었다.

SELECT 
ROUND(CAST(SUM(CASE WHEN b.country_id <> c.country_id THEN 1 ELSE 0 END)*100 AS DECIMAL)/count(*),1)
FROM phone_calls a
LEFT JOIN phone_info b ON a.caller_id = b.caller_id
LEFT JOIN phone_info c ON a.receiver_id = c.caller_id;

컬럼명이 달라도 테이블을 조인할 수 있었다!! FROM phone_calls a LEFT JOIN phone_info b ON a.caller_id = b.caller_id LEFT JOIN phone_info c ON a.receiver_id = c.caller_id 위처럼 똑같은 phone_info 테이블을 각각 b,c로 지정하여 풀어낼 수 있다.

- 현재 만족하고 있는 부분, 계속 이어갔으면 하는 부분

• 조장으로서 책임감 때문에 스트레스가 다소 있었던 것 같다. 하지만 나는 리더 재질인가보다. 스트레스를 받음에도 불구하고 리더인게 편하고 내가 맡아서 주체적으로 진행하는 것이 좋다. 그래서 앞으로도 계속 리더를 맡으며 프로젝트를 진행하고 싶다.

Problem

- 불편하게 느끼는 부분, 개선이 필요하다고 생각되는 부분

• 내가 아직 리더로서 서투른 부분이 많다. 남들에게는 많은 일을 주기 꺼려하고 온전히 일을 팀원에게 다 맡기는 것이 솔직히 믿음이 100프로 가지는 않는다. 그래서 내가 많이 일을 자처해서 하려다보니 나에게 더 스트레스로 다가오는 부분이 있지 않았었나 싶다. 또한 각자의 일을 매번 공유하는 일에 있어 불편한 점이 있었다.

Try

- Problem에 대한 해결책

- 다음 회고 때 판별 가능한 것

- 당장 실행 가능한 것

• 우선 팀원들 믿고 그들이 제일 잘 할 수 있는 일들을 분배해서 맡기는 것이 필요하다고 생각한다. 앞으로는 좀 더 팀원들을 믿으려고 한다. 실제로 현재 팀원들 덕분에 막판에 내가 할일들이 확 줄어들었다. 초중반까지는 내가 어떻게든 다하고 이끄려고 했는데 막판에는 팀원들을 좀 믿고 맡기니 잘 수행해주어 감사하다. 우선 팀원들에게 각자의 역할을 명확하게 정의하고 각 팀원에게 잘 맡는 일을 부여해주는 것이 가장 베스트인 것 같다. 또한 각자의 작업들을 잘 공유할 수 있는 툴은 무조건 사용해야겠다고 생각했다. 문서 같은 경우에는 구글독스나 시트를 활용하며 코드 공유는 깃허브를 꼭 사용해야겠다 생각했다. 깃허브를 아직 사용할줄 잘 몰라 이용하지 않았는데 꼭 공부하여 담부터는 써야겠다는 생각을 했다.

SELECT PRODUCT_ID,
       PRODUCT_NAME,
       PRODUCT_CD,
       CATEGORY,
       PRICE
FROM FOOD_PRODUCT
WHERE PRICE = MAX(PRICE)

하지만 계속 오류가 떴다.. WHERE 조건절에 MAX(PRICE)를 넣으면 풀려야 하는거 아니야? 생각했다. 근데 알아보니.. WHERE절에 직접 집계함수 OR 윈도우함수의 결과를 사용해선 안된다고 한다... 대신 아래와 같이 서브쿼리를 활용하여 해결 가능했다.

-- 
SELECT PRODUCT_ID,
       PRODUCT_NAME,
       PRODUCT_CD,
       CATEGORY,
       PRICE
FROM FOOD_PRODUCT
WHERE PRICE = (SELECT MAX(PRICE) FROM FOOD_PRODUCT)

또 다른 문제를 풀다가 알게된 사실이 있다!! 아래의 문제를 봐보자. 여기서 딱 생각이 들었던게 서브쿼리랑 IF문, LIKE문을 써야겠다 싶었다. 그래서 아래와 같이 쿼리를 작성했다.

-- 코드를 입력하세요
SELECT ANIMAL_ID,
       NAME,
       IF(중성화여부='중성화','O','X') "중성화"
FROM
(
SELECT ANIMAL_ID,
       NAME,
       IF ((SEX_UPON_INTAKE like 'Neutered%' OR 'Spayed%'),'중성화','정상') '중성화여부'
from ANIMAL_INS
)a
ORDER BY 1

하지만 계속 오류가 났다ㅜㅜ 이유를 알고보니 LIKE문 안에는 OR가 들어갈 수 없단다!! 그래서 구지구지 IF문을 아래와 같이 길게 써야했다..

IF((SEX_UPON_INTAKE LIKE 'Neutered%') OR (SEX_UPON_INTAKE LIKE 'Spayed%'),'중성화','정상') '중성화여부'

이렇게 쓰니 해결이 되었다.. 아직 모르는게 투성이다ㅜ 문제를 많이 풀면 풀수록 이런 것들을 알게되는 것같다. 꾸준히 많이 풀어봐야겠다!

오늘의 첫 문제는 아래와 같았어요 저는 처음 보고 아래와 같은 코드를 써서 바로 통과가 되었어요!

SELECT ANIMAL_TYPE,
       CASE WHEN NAME IS NULL THEN 'No name'
       ELSE NAME END NAME,
       SEX_UPON_INTAKE
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

다른 사람들은 어떻게 풀었나 보았는데 아래 처럼 푼 사람들이 훨씬 많은거에요!

SELECT ANIMAL_TYPE,
       COALESCE(NAME, 'No name') NAME,
       SEX_UPON_INTAKE
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

여기서 저는 'COALESCE'가 뭔지 몰랐어요 뭔지 모르지만 훨씬 함수가 간단해지는걸 보고 알고싶어져서 알아보았어요~

COALESCE는 주로 정해져있지 않은 NULL값을 변환할때 쓰이더라구요

예를 들어 위 문제에서는 COALESCE(NAME, 'No name') 이라고 함은 NAME 열에 값이 있으면 NAME을 그대로 불러오고, 열에 값이 없으면 NULL 대신 'No name'을 불러오라는 뜻이에요

두번째 문제는 아래와 같았어요 저는 아래와 같은 쿼리로 통과를 하였어요!

SELECT ANIMAL_ID,
       NAME,
       SUBSTRING(DATETIME,1,10) '날짜'
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

여기서 날짜 정보에는 .'2018-01-12 14:20:13' 이런식으로 적혀있었는데 '2018-01-12' 이런 식으로 답을 내라고 하여서 저는 제가 배운 SUBSTRING이란 함수를 이용하여 날짜 열의 앞에서부터 10자리만 가져오는 식으로 풀이를 했어요

근데 다른 사람들의 풀이를 보니 아래와 같이 더욱 간단하게 푸신 분들이 많더라구요!

SELECT ANIMAL_ID , NAME , DATE_FORMAT(DATETIME, '%Y-%m-%d') AS DATETIME
FROM ANIMAL_INS

DATE_FORMAT 이라는 함수를 통해 더욱 좋은 쿼리를 만들 수 있었어요! 아래는 GPT4가 친절히 알려준 설명이에요 아래는 사용 예시입니다! 본 문제에서는 *DATE_FORMAT(DATETIME, '%Y-%m-%d') * 이렇게 함으로서 DATETIME 정보를 0000년-00월-00일 로만 쉽게 나타낼 수 있었어요~

SELECT CASE WHEN DATETIME = MIN(DATETIME) THEN NAME
       ELSE NULL
       END 'NAME'
FROM ANIMAL_INS
WHERE NAME IS NOT NULL

이렇게 치니 아래와 같은 결과가 나왔다. 딸랑 이렇게 나온것이다😦

도무지 알 수가 없어 CHATGPT 선생님한테 물어보니 OVER절을 알려주셨다. 나는 왜 꼭 OVER가 들어가야하는지 물어보았다.

요약하자면 MIN(최소함수),MAX(최대함수) 등은 GROUP BY와 같이 그룹별로 쓰인다. GROUP BY 없이 쓴다면 개별그룹으로 인식하고 단일값을 생성한다. 그리하여 다른 그룹들고 비교하고 싶다면 'OVER()'을 넣어주기만 하면 된다. 그리하여 결국 답이 나왔다! 하지만... 정답이 아니란다ㅜㅜ 답은 똑같은데 계속 틀리다고 통과가 되지 않았다.. 그리하여 다시 물어보았다 정답엔 거의 가까웠지만 효율적이지 않은 쿼리라고 말해주었다..ㅠ 'LIMIT'이라는 걸 쓰면 너무 효율적으로 해결이 되었다!! 나는 배워본 적이 없어서 몰랐다..

LIMIT으로 가져올 최대 행수를 지정해 줄 수 있었다. 기본 문법은 LIMIT;(가져올 행수) 이다.

같이 콤보로 쓰이는 것 중에 OFFSET이라는 것도 있었다. OFFSET은 행수를 건너뛰고 시작하고 싶을 때 쓰인다. 예를 들어 테이블에서 10번 행을 건너뛰고 11번째의 행만 가져오고 싶다면 아래처럼 쓰면 된다

LIMIT 1 OFFSET10 ;

결국 LIMIT을 활용하여 간단하게 문제를 풀 수 있었다! 힘이 빠지긴 했지만 좋은거 알게되어 기쁘다😄

SELECT NAME,
       COUNT(NAME) 'COUNT'
FROM ANIMAL_INS
WHERE NAME<>'NULL' AND COUNT(NAME)>1
GROUP BY NAME
ORDER BY NAME

하지만 계속 틀렸다고 나오는 것이다.. 뭐가 잘못되었는지 도무지 알지 못하여 나의 선생님 CHATGPT4 께 여쭈어보았다.

CHATGPT4 선생님께서는 HAVING절이라는 것을 통해 한번에 해결하셨다.. 답은 아래와 같다.

SELECT NAME,
       COUNT(NAME) 'COUNT'
FROM ANIMAL_INS
WHERE NAME <> 'NULL'
GROUP BY NAME 
HAVING COUNT(NAME)>1 
ORDER BY NAME

나는 아직 HAVING절을 배워본 적이 없기 때문에 HAVING과 WHERE절에 대한 차이가 궁금했다. 물어본 결과 선생님의 대답은 이러했다.

쉽게 이야기해 WHERE절은 그룹화되기 전에 적용되고 HAVING절은 그룹화된 후 적용된다. 그래서 HAVING절은 GROUP BY 절 이후에 위치한다. 그룹화된 결과에 대해 집계 함수를 포함한 조건을 지정하는데 사용된다.

결국 테스트에서 결과로 원했던 것은 동물 이름 집단에서 2개 이상의 이름 집단을 원했기에, 그룹화된 결과에 대한 조건임으로 'HAVING'절이 필요했던 것이다.

이제는 어느정도 HAVING절을 이해할 수 있게 된 것같다😄