TIL(Today I Learned)62일차(24.02.09)

SQL, PYTHON 예제

1. SQL

https://leetcode.com/problems/immediate-food-delivery-ii/description/

WITH FIRST AS (
    SELECT DELIVERY_ID, CUSTOMER_ID, ORDER_DATE, CUSTOMER_PREF_DELIVERY_DATE,
       RANK() OVER(PARTITION BY CUSTOMER_ID ORDER BY ORDER_DATE) RANKING
    FROM DELIVERY
),
SECOND AS (
   SELECT DELIVERY_ID, CUSTOMER_ID, ORDER_DATE, CUSTOMER_PREF_DELIVERY_DATE
   FROM FIRST 
   WHERE RANKING = 1
),
THIRD AS (
     SELECT DELIVERY_ID,
       CUSTOMER_ID,
       ORDER_DATE,
       CUSTOMER_PREF_DELIVERY_DATE,
       IF(ORDER_DATE = CUSTOMER_PREF_DELIVERY_DATE, 'IMMEDIATE', 'SCHEDULED') D   
    FROM SECOND
)
SELECT ROUND((COUNT(*) / (SELECT COUNT(*) FROM THIRD)) * 100, 2) IMMEDIATE_PERCENTAGE
FROM THIRD
WHERE D = 'IMMEDIATE'

 

 

2. PYTHON 

https://school.programmers.co.kr/learn/courses/30/lessons/12947

def solution(x):
    answer = 0
    val = str(x)
    for i in range(len(val)):
        answer+=int(val[i])
    val2 = answer
    if x % val2 == 0:
        return True
    else:
        return False

 

https://school.programmers.co.kr/learn/courses/30/lessons/12912

def solution(a, b):
    answer = 0
    if a < b:
        for i in range(a, b+1):
            answer+=i    
    elif a==b:
        answer+=a
    else:
        for i in range(b, a+1):
            answer+=i
    return answer

 

SQLD(13주차)

 

심화 프로젝트

DAU 구하기

# data type을 날짜로 변환
train_df2['date'] = train_df2['date'].apply(lambda x: pd.to_datetime(str(x), format = '%Y%m%d'))

# DAU 라인 차트로 표시
a = train_df2.groupby(['date'], as_index=False).agg({'fullVisitorId' : pd.Series.nunique})
sns.lineplot(data = a, x = 'date', y = 'fullVisitorId')