🌻

[250521] 데일리 스크럼

진행일자

2025/05/21

선택

2주차

특이사항

[학습 시작 전 - 목표 설정]

이름	이번 주 학습 목표	오늘 학습 목표
라혜선	SQL 복습하기 JOIN 개념 빡숙!!, 파이썬 사전캠프 강의 듣기, 라이브 세션 과제 풀기	파이쎤 사전캠프 강의 듣기, JOIN 책으로 개념 훑어보기, 라이브 세션 듣고 정리해서 TIL 작성하기, 라이브 세션 문제 풀고 제출
신주연	SQL 복습하기/세션 듣기/문제 풀어보기	SQL 복습하기/세션 듣기/문제 풀어보기
송인영	SQL 학습하기	SQL 복습 및 4회치 세션 듣기 / 4회차 과제 함께 풀기
홍정민	SQL 자습 및 정리/SQL 과제 풀이	SQL 자습 및 정리
노시현	SQL 개인 자습 및 정리 / 개인 과제 완료	SQL 과제 4-6번 복습, 라이브 세션 강의 및 과제, SQLD 공부, TIL 작성

[ 학습 후 - 목표 달성 여부 & 회고]

이름	오늘 달성한 목표	오늘 달성하지 못한 목표 & 이유 (회고)
	오전/오후
라혜선	파이썬 강의 듣기!!! join 책으로 공부하기, 라이브 세션 문제 풀고 제출, 라이브 세션 문제 풀이 TIL 작성	라이브 세션 내용 TIL 작성 ︎ 과제 난이도가 올라서 정리하고 3번 문제를 꼭꼭 씹어서 이해하는데 시간이 오래 걸렸다.
송인영	오전 : SQL 3회차 세션 복습하기 및 4회차 수강 오후 : 팀원들과 4회차 과제 학습하기 및 복습하기
노시현	SQL 개인 과제 4번 복습, SQL 라이브 세션 4회차 4회차 과제 팀 활동, 파이썬 강의 1/4 수강 및 복습	개인 과제 5-6번 복습, SQLD 공부 → 라이브 세션 과제에 시간 , 집중도 ㅜㅜ
신주연	SQL 복습하기/세션 듣기/문제 풀어보기 완료
홍정민	오전 : SQL 라이브 세션 수강/ 4회차 과제 학습 및 공유	SQL 자습 및 정리&라이브 세션 및 과제로 인한 집중도 하락

CHECK TIL ?

> TIL 은 매일 작성하기 !

> WIL은 매주 금요일 작성하기!

이름	TIL 및 WIL 주소
라혜선	https://velog.io/@hsla/SQL-문제풀이5
홍정민
송인영
신주연	https://velog.io/@wndus0926/내일배움캠프-과제-정리
노시현	[내일배움캠프] SQL 문제 풀이 (4)

WE ARE TIM 4회차 라이브 세션 과제

라라

-- 문제 1.
SELECT 
	COUNT(DISTINCT msu.game_account_id) AS usercnt
	,CASE WHEN pay_type IS NOT NULL THEN '결제함'
		  ELSE '결제안함' END AS gb
FROM marketer_sql_users AS msu
 	LEFT JOIN marketer_sql_payment msp 
	ON msu.game_account_id  = msp.game_account_id
GROUP BY gb
;

-- 문제 2.
SELECT 
	 sub.game_account_id
	,sub.sumamount
	,sub.actor_cnt
FROM
(
	SELECT 
		msu.game_account_id
		,SUM(msp.pay_amount) AS sumamount
		,COUNT(DISTINCT msu.game_actor_id) AS actor_cnt
	FROM marketer_sql_users AS msu 
		INNER JOIN marketer_sql_payment AS msp
		ON msu.game_account_id = msp.game_account_id
	WHERE msu.serverno >=2 AND msp.pay_type = 'CARD'
	GROUP BY game_account_id
	ORDER BY sumamount DESC
) AS sub
WHERE actor_cnt >= 2
;

-- 문제 3.

SELECT 
	sub.serverno
	,ROUND(AVG(diffdate),0) AS avgdiff
FROM 
(
	SELECT 
	 	msu.game_account_id
		,first_login_date
		,serverno
		,MAX(DATE_FORMAT(msp.approved_at, '%Y-%m-%d %H:%i:%s')) AS date2
		,DATEDIFF(
		MAX(DATE_FORMAT(msp.approved_at, '%Y-%m-%d %H:%i:%s'))
		,DATE_FORMAT(msu.first_login_date,'%Y-%m-%d')
) AS diffdate
	FROM marketer_sql_users AS msu 
		INNER JOIN marketer_sql_payment AS msp
		ON msu.game_account_id = msp.game_account_id
		WHERE DATE_FORMAT(msu.first_login_date, '%Y-%m-%d') < DATE_FORMAT(msp.approved_at,'%Y-%m-%d %H:%i:%s')
		GROUP BY msu.game_account_id
) AS sub
WHERE diffdate >= 10
GROUP BY sub.serverno
ORDER BY sub.serverno DESC
;

-- 튜터님의 조언대로 수정한 쿼리 

SELECT 
	 serverno
	,ROUND(AVG(diffdate),0) AS avgdiff
FROM 
(
	SELECT 
	 	 msu.game_account_id
		,msu.first_login_date
		,msu.serverno
		,DATEDIFF(subp.date2, msu.first_login_date) AS diffdate
	FROM marketer_sql_users AS msu
		INNER JOIN 
		(
			SELECT 
					game_account_id
				 ,MAX(approved_at) AS date2
			FROM marketer_sql_payment
		    GROUP BY game_account_id
		) AS subp
	   	ON msu.game_account_id = subp.game_account_id
		WHERE subp.date2 > msu.first_login_date
) AS t
WHERE diffdate >= 10
GROUP BY serverno
ORDER BY serverno DESC
;
SQL
복사

정민

1.
SELECT IF(pay_amount IS NULL,'결제안함','결제함') AS gb,
		COUNT(DISTINCT mu.game_account_id) AS usercnt
FROM marketer_sql_users mu 
LEFT JOIN marketer_sql_payment mp
ON mu.game_account_id = mp.game_account_id
GROUP BY gb

2.
SELECT a.game_account_id,
		a.actor_cnt,
		a.sumamount
FROM
(
SELECT mu.game_account_id,
		COUNT(DISTINCT game_actor_id) AS actor_cnt,
		SUM(pay_amount) AS sumamount
FROM marketer_sql_users mu 
INNER JOIN marketer_sql_payment mp 
ON mu.game_account_id = mp.game_account_id
WHERE serverno >= 2 
AND pay_type ='CARD'
GROUP BY game_account_id
ORDER BY sumamount DESC
) AS a
WHERE actor_cnt>=2

3.
SELECT serverno,
		ROUND(AVG(a.diffdate),0) AS avgdiffdate
FROM
(
SELECT mu.game_account_id,
		mu.first_login_date,
		mu.serverno,
		MAX(mp.approved_at) AS date2,
		DATEDIFF(MAX(mp.approved_at), first_login_date) AS diffdate
FROM marketer_sql_users mu 
INNER JOIN marketer_sql_payment mp 
ON mu.game_account_id = mp.game_account_id
GROUP BY mu.game_account_id
HAVING mu.first_login_date < MAX(mp.approved_at)
) AS a
WHERE a.diffdate >=10
GROUP BY serverno 
ORDER BY serverno DESC
SQL
복사

인영

1번 문제
SELECT CASE
	WHEN msp.game_account_id IS NOT NULL THEN '결제함'
	ELSE '결제안함'
END AS gb,
COUNT(DISTINCT msu.game_account_id) AS usercnt
FROM marketer_sql_users msu
LEFT JOIN marketer_sql_payment msp
ON msu.game_account_id = msp.game_account_id
GROUP BY 1

2번 문제
SELECT game_account_id, actor_cnt, sumamount
FROM (
SELECT msu.game_account_id,
COUNT(DISTINCT msu.game_actor_id) AS actor_cnt,
SUM(msp.pay_amount) AS sumamount
FROM marketer_sql_users msu
JOIN marketer_sql_payment msp
ON msu.game_account_id = msp.game_account_id
WHERE serverno >= 2 AND pay_type='card'
GROUP BY 1
) AS A
WHERE A.actor_cnt>=2
ORDER BY A.sumamount desc

3번 문제
#핵어렵다
SELECT serverno,
ROUND(AVG(diffdate)) AS avgdiffdate
FROM 
(
SELECT u.serverno, p.date2,
DATEDIFF(p.date2,u.first_login_date) diffdate
FROM marketer_sql_users u
JOIN
(
SELECT game_account_id, MAX(approved_at) AS date2
FROM marketer_sql_payment
GROUP BY game_account_id
) AS p
ON u.game_account_id = p.game_account_id
WHERE first_login_date < p.date2
AND DATEDIFF(p.date2,u.first_login_date)>= 10
) AS sub
GROUP BY 1
ORDER BY 1 DESC
JavaScript
복사

주연

1번 문제

SELECT
    CASE 
        WHEN p.game_account_id IS NULL THEN '결제안함'
        ELSE '결제함'
    END AS gb,
    COUNT(distinct u.game_account_id) AS usercnt
FROM marketer_sql_users u
LEFT JOIN marketer_sql_payment p ON u.game_account_id = p.game_account_id
GROUP BY 1

2번 문제

SELECT game_account_id,
 actor_cnt,
 sumamount
FROM (
    SELECT 
        u.game_account_id,
        COUNT(DISTINCT u.game_actor_id) AS actor_cnt,
        SUM(p.pay_amount) AS sumamount
    FROM marketer_sql_users u
    INNER JOIN marketer_sql_payment p 
        ON u.game_account_id = p.game_account_id
    WHERE u.serverno >= 2
      AND p.pay_type = 'CARD'
    GROUP BY u.game_account_id
) sumamount
WHERE actor_cnt >= 2
ORDER BY sumamount DESC

3번 문제


SELECT 
    a.serverno,
    ROUND(AVG(a.diffdate)) AS avgdiffdate
FROM (
    SELECT 
        u.game_account_id,
        u.first_login_date,
        u.serverno,
        p.date2,
        DATEDIFF(p.date2, u.first_login_date) AS diffdate
    FROM marketer_sql_users u 
    INNER JOIN (
        SELECT 
            game_account_id, 
            MAX(approved_at ) AS date2
        FROM 
         marketer_sql_payment p 
        GROUP BY 
            1
    ) p
    ON u.game_account_id = p.game_account_id
    WHERE p.date2 > u.first_login_date
) a
WHERE a.diffdate >= 10
GROUP BY 1
ORDER BY a.serverno DESC


JavaScript
복사

시현

1번 문제.

SELECT CASE WHEN p.game_account_id IS NULL THEN '결제안함'
       ELSE '결제함' END AS gb,
       COUNT(DISTINCT u.game_account_id) AS usercnt
FROM marketer_sql_users AS u LEFT JOIN marketer_sql_payment AS p ON u.game_account_id=p.game_account_id
GROUP BY gb;

2번 문제.

SELECT game_account_id, actor_cnt, sumamount
FROM
(
SELECT u.game_account_id, 
       COUNT(DISTINCT u.game_actor_id) AS actor_cnt,
       SUM(p.pay_amount) AS sumamount
FROM marketer_sql_users AS u INNER JOIN marketer_sql_payment AS p ON u.game_account_id=p.game_account_id
WHERE u.serverno >= 2 AND p.pay_type='CARD'
GROUP BY game_account_id
) a
WHERE actor_cnt >=2
ORDER BY sumamount DESC;

3번 문제.
SELECT serverno,
       ROUND(AVG(diffdate),0) avgdiffdate
FROM
(
SELECT serverno, 
      DATEDIFF(date2, first_login_date) AS diffdate
FROM
(
SELECT u.game_account_id, 
       u.first_login_date, 
       u.serverno,
       LEFT(date2, 10) date2
FROM marketer_sql_users AS u 
INNER JOIN (SELECT game_account_id, MAX(approved_at) AS date2 FROM marketer_sql_payment GROUP BY game_account_id) AS p 
ON u.game_account_id=p.game_account_id
WHERE first_login_date < date2
) a
) b
WHERE diffdate >=10
GROUP BY serverno
ORDER BY serverno DESC;
JavaScript
복사

1번 문제 출력 값

2번 문제 출력 값

3번 문제 출력 값