[sql] 날짜별 이동평균, 월간 누적매출, 매출 z차트 만들기
날짜별 이동평균, 월간 누적매출 DROP TABLE IF EXISTS purchase_log; CREATE TABLE purchase_log( dt varchar(255) , order_id integer , user_id varchar(255) , purchase_amount integer ); INSERT INTO purchase_log VALUES ('2014-01-01', 1, 'rhwpvvitou', 13900) , ('2014-01-01', 2, 'hqnwoamzic', 10616) , ('2014-01-02', 3, 'tzlmqryunr', 21156) , ('2014-01-02', 4, 'wkmqqwbyai', 14893) , ('2014-01-03', 5, 'ciecbedwbq', 130..
2022. 9. 26.
[sql] 누적 접속자 쿼리, 최초 구매
누적접속자 selectaa.date ,aa.server ,count(distinct accountid) --dau ,sum(count(x)) over (partition by server order by aa.date) -- 누적 접속자 from ( selectdate ,server ,accountid ,row_number() over(partition by server, accountid order by date asc) as x fromworkdb2.dbo.dau2 with (nolock) where date between '2020-07-01' and '2020-07-02' ) as aa group by aa.date ,aa.server 최초구매 selectaaa.productname, suM(aa..
2022. 9. 26.
[SQL] 잔존율 retention 집계 하기 D+7, M+1
use workdb2 go WITH ##repeat_interval AS ( SELECT * FROM ( VALUES ('01 day repeart', 1) ,('02 day repeart', 2) ,('03 day repeart', 3) ,('04 day repeart', 4) ,('05 day repeart', 5) ,('06 day repeart', 6) ,('07 day repeart', 7) ) AS a (index_name, interval_date) ) ,##rau_0501_0531 AS ( selectcast(time as date) 'date', server, accountid fromexample2.dbo.all_rau where cast(time as date) between '202..
2022. 9. 26.