오늘은 과제 정리를 해보겠습니다.
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
2. 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
3. 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
4.
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
5.
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
