[
학습 시작 전 - 목표 설정]
이름 | 이번 주 학습 목표 | 오늘 학습 목표 |
라혜선 | SQL 복습하기 JOIN 개념 빡숙!!, 파이썬 사전캠프 강의 듣기, 라이브 세션 과제 풀기 | 파이쎤 사전캠프 강의 듣기, JOIN 책으로 개념 훑어보기, 라이브 세션 듣고 정리해서 TIL 작성하기, 라이브 세션 문제 풀고 제출 |
신주연 | SQL 복습하기/라이브 세션 듣기 | SQL 3주차 강의 복습하기/라이브 세션 듣기 |
| 송인영 | SQL 라이브 세션 학습하기 | 라이브 세션 듣고 문제 풀기 |
홍정민 | SQL 자습 및 정리/SQL 개인 과제풀이 | SQL 자습 및 정리/ SQL 팀 문제풀이 |
노시현 | SQL 공부 | SQL 개인 과제 1-3번 복습, 라이브 세션 강의, 라이브 세션 문제 풀이, 파이썬 강의 복습 |
[ 학습 후 - 목표 달성 여부 & 회고]
이름 | 오늘 달성한 목표 | 오늘 달성하지 못한 목표 & 이유 (회고) |
오전/오후 | ||
라혜선 | 라이브 세션 문제 풀고 제출, join 책으로 개념 훑어보기, 라이브 세션 정리해서 TIL 작성하기, 라이브세션 문제 풀이 벨로그에 모두 업로드 | ㅎㅎ…ㅎ…파이썬 강의 듣기… ….오늘 매니저님 말도 들었으니까 더 이상 미루지 말고 진짜 진짜의 최종의 마지막 내일 들을게요… 이제는 더 이상 물러날 곳이 없다. |
| 송인영 | 오전 : SQL 복습하기 및 3회차 세션 수강
오후 : SQL 3회차 과제 풀기 및 팀원들과 맞춰보기 | |
노시현 | SQL 개인 과제 1-3번 복습, TIL, SQL 라이브 세션 강의 | |
신주연 | SQL 복습완료/라이브 세션 듣기 완료 | |
홍정민 | SQL 라이브 세션 수강, 팀 문제 풀이 및 과제 제출 |
CHECK TIL ?
> TIL 은 매일 작성하기 !
> WIL은 매주 금요일 작성하기!
WE ARE TIM 3회차 라이브 세션 과제
이름 | 1번 문제 작성 쿼리 | 2번 문제 작성 쿼리 | 3번 문제 작성 쿼리 | 4번 문제 작성 쿼리 | 5번 문제 작성 쿼리 |
라혜선 | SELECT
serverno
,LEFT(first_login_date, 7) AS m
,COUNT(DISTINCT game_account_id) AS usercnt
FROM marketer_sql_users msu
GROUP BY serverno, m
; | SELECT first_login_date
,COUNT(DISTINCT game_actor_id) AS actor_cnt
FROM marketer_sql_users msu
GROUP BY first_login_date
HAVING COUNT(DISTINCT game_actor_id) > 10
; | SELECT
serverno
,IF(first_login_date >= '2024-01-01','신규유저', '기존유저') AS gb
,COUNT(DISTINCT game_actor_id) AS actor_cnt
,AVG(level) AS avg_level
FROM marketer_sql_users msu
GROUP BY serverno, gb
; | SELECT sub.first_login_date
,sub.actor_cnt
FROM
(
SELECT first_login_date
,COUNT(DISTINCT game_actor_id) AS actor_cnt
FROM marketer_sql_users
GROUP BY first_login_date
) AS sub
WHERE sub.actor_cnt > 10
GROUP BY sub.first_login_date, sub.actor_cnt
; | SELECT sub.actor_cnt
,COUNT(DISTINCT game_account_id) AS accnt
FROM
(
SELECT COUNT(DISTINCT game_actor_id) AS actor_cnt
,game_account_id
FROM marketer_sql_users msu
WHERE level >= 30
GROUP BY game_account_id
HAVING COUNT(game_actor_id) >= 2
) AS sub
GROUP BY sub.actor_cnt
; |
홍정민 | SELECT serverno,
SUBSTR(first_login_date,1,7) AS m,
COUNT(distinct game_account_id) AS
FROM marketer_sql_users msu
GROUP BY serverno, m | SELECT first_login_date,
COUNT(DISTINCT game_actor_id) AS actor_cnt
FROM marketer_sql_users msu
GROUP BY first_login_date
HAVING actor_cnt > 10 | SELECT serverno,
IF(first_login_date < '2024-01-01', '기존유저', '신규유저') AS gb,
COUNT(DISTINCT game_actor_id) AS actor_cnt,
AVG(level) AS avg_level
FROM marketer_sql_users msu
GROUP BY serverno, gb | SELECT a.first_login_date,
a.actor_cnt
FROM
(
SELECT first_login_date,
COUNT(DISTINCT game_actor_id) AS actor_cnt
FROM marketer_sql_users
GROUP BY first_login_date
) AS a
WHERE a.actor_cnt > 10 | SELECT a.actor_cnt,
COUNT(game_account_id) AS accnt
FROM
(
SELECT COUNT(game_actor_id) AS actor_cnt,
game_account_id
FROM marketer_sql_users msu
WHERE level >= 30
GROUP BY game_account_id
HAVING actor_cnt >= 2
) AS a
GROUP BY a.actor_cnt
ORDER BY a.actor_cnt |
| 송인영 | SELECT serverno,
LEFT(first_login_date, 7) AS month,
COUNT(DISTINCT game_account_id) AS user_cnt
FROM marketer_sql_users
GROUP BY 1,2 | SELECT first_login_date,
COUNT(DISTINCT game_account_id) AS actor_cnt
FROM marketer_sql_users
GROUP BY first_login_date
HAVING COUNT(DISTINCT game_actor_id) > 10 | SELECT serverno,
CASE WHEN first_login_date < '2024-01-01' THEN '기존'
ELSE '신규'
END AS gb,
COUNT(DISTINCT game_actor_id) AS actor_count,
AVG(level) AS avg_level
FROM marketer_sql_users
GROUP BY 1,2 | SELECT *
FROM
(
SELECT first_login_date,
COUNT(DISTINCT game_account_id) AS actor_cnt
FROM marketer_sql_users
GROUP BY first_login_date
) AS a
WHERE actor_cnt > 10 | SELECT actor_count,
COUNT(*) as accnt
FROM
(
SELECT game_account_id,
COUNT(DISTINCT game_actor_id) as actor_count
FROM marketer_sql_users
WHERE level >= 30
GROUP BY 1
HAVING COUNT(DISTINCT game_actor_id) >=2
) AS a
GROUP BY 1 |
신주연 | SELECt serverno,
SUBSTR(first_login_date, 1, 7) AS m,
COUNT(DISTINCT game_account_id) AS usercnt
FROM marketer_sql_users msu
GROUP BY
serverno,
SUBSTR(first_login_date, 1, 7)
ORDER BY
serverno,
m | select first_login_date,
count(distinct(game_actor_id)) actor_cnt
from marketer_sql_users msu
group by 1
HAVING count(distinct(game_actor_id))>10 | select serverno,
case when first_login_date <'2024-01-01' then '기존유저'
else '신규유저'
end gb,
count(distinct(game_actor_id)) actor_cnt,
avg(level) avg_level
from marketer_sql_users msu
group by 1,2 | SELECT
first_login_date,
actor_cnt
FROM (
SELECT
first_login_date,
COUNT(DISTINCT game_actor_id) AS actor_cnt
FROM
marketer_sql_users
GROUP BY
first_login_date
) sub
WHERE
actor_cnt > 10
| SELECT actor_cnt,
COUNT(DISTINCT game_account_id) AS accnt
FROM (
SELECT game_account_id,
COUNT(DISTINCT game_actor_id) AS actor_cnt
FROM marketer_sql_users
WHERE level >= 30
GROUP BY game_account_id
HAVING COUNT(DISTINCT game_actor_id) >= 2
) AS sub
GROUP BY actor_cnt
ORDER BY actor_cnt |
노시현 | SELECT serverno,
LEFT(first_login_date,7) m,
COUNT(DISTINCT game_account_id) user_cnt
FROM marketer_sql_users
GROUP BY serverno, m; | SELECT first_login_date,
COUNT(DISTINCT game_actor_id) actor_cnt
FROM marketer_sql_users
GROUP BY first_login_date
HAVING actor_cnt > 10 ; | SELECT serverno,
IF(first_login_date < ‘2024-01-01’, '기존유저', '신규유저') gb,
COUNT(DISTINCT game_actor_id) actor_cnt,
AVG(level) avg_level
FROM marketer_sql_users
GROUP BY serverno; | SELECT a.first_login_date,
a.actor_cnt
FROM
(
SELECT first_login_date,
COUNT(DISTINCT game_actor_id) actor_cnt
FROM marketer_sql_users
GROUP BY first_login_date
) AS a
WHERE a.actor_cnt > 10; | SELECT a.actor_cnt,
COUNT(game_account_id) acc_cnt
FROM
(
SELECT COUNT(game_actor_id) actor_cnt,
game_account_id
FROM marketer_sql_users
WHERE level >= 30
GROUP BY game_account_id
HAVING COUNT(game_actor_id) >= 2
) a
GROUP BY a.actor_cnt
ORDER BY a.actor_cnt; |
1번 문제 출력 값
2번 문제 출력 값
3번 문제 출력 값
4번 문제 출력 값
5번 문제 출력 값