문제:
박지완
select
c.serverno,
round(avg(c.diffdate))as avgdiffdate
from
(select datediff(date(b.date2), a.date1)as diffdate, a.serverno
from
(select game_account_id, min(first_login_date)as date1, serverno
from users
where ip_addr like "70%"
group by game_account_id, serverno
)a
inner join
(select game_account_id, max(approved_at)as date2
from payment
group by game_account_id
)b
on a.game_account_id = b.game_account_id
where b.date2 > a.date1
)c
where c.diffdate >= 10
group by c.serverno
order by c.serverno desc
SQL
복사
배시환
select c.serverno,
round(avg(c.diffdate ), 0) as avgdiffdate
from( select a.serverno ,
DATEDIFF(b.date2, a.first_login_date )as diffdate
from( select u.game_account_id ,
u.serverno, min(u.first_login_date ) as first_login_date
from ( select game_account_id ,
first_login_date ,
serverno
from users
where ip_addr like ('70%')
) as u
group by u.game_account_id , u.serverno
) as a
inner join
( select game_account_id,
max(approved_at) as date2
from payment
group by game_account_id
) as b
on a.game_account_id = b.game_account_id
where date2 > a.first_login_date
and DATEDIFF(b.date2, a.first_login_date ) >= 10
) as c
group by c.serverno
order by c.serverno desc
SQL
복사
이하진
select serverno, round(avg(diffdate)) avgdiffdate
from(
select serverno, datediff(date(date2), date1) diffdate
from(
select game_account_id, serverno, min(first_login_date) date1
from basic.users
where ip_addr like '70%'
group by 1, 2
) u
inner join(
select game_account_id, max(approved_at) date2
from basic.payment
group by 1
) p
on u.game_account_id=p.game_account_id
where date1<date2
) a
where diffdate>=10
group by 1
order by 1 desc
SQL
복사
김종선
select
c.serverno ,
ROUND(AVG(c.diffdate)) as avgdiffdate
from
(
select
a.serverno ,
DATEDIFF(b.date2 , a.first_login ) as diffdate
from
(
select
game_account_id ,
min(first_login_date) as first_login ,
serverno
from
basic.users
where
ip_addr like '70%'
group by
game_account_id, serverno
) a
inner join
(
select
game_account_id,
MAX(approved_at) as date2
from
basic.payment
group by
game_account_id
) b
on a.game_account_id = b.game_account_id
where
b.date2 > a.first_login
) c
where
c.diffdate >= 10
group by
c.serverno
order by
serverno desc
SQL
복사