Postgresql 방문자, 가입자 통계 (개월, 주, 일)

--전체 가입자 수 (최근 6개월간)
SELECT
  COUNT(case when date_trunc('month' , "createdAt") = date_trunc('month', now())  then 1 end) as thismonth,
  COUNT(case when date_trunc('month' , "createdAt") = date_trunc('month', now() - interval '1 months') then 1 end) as lastmonth1,
  COUNT(case when date_trunc('month' , "createdAt") = date_trunc('month', now() - interval '2 months') then 1 end) as lastmonth2,
  COUNT(case when date_trunc('month' , "createdAt") = date_trunc('month', now() - interval '3 months') then 1 end) as lastmonth3,
  COUNT(case when date_trunc('month' , "createdAt") = date_trunc('month', now() - interval '4 months') then 1 end) as lastmonth4,
  COUNT(case when date_trunc('month' , "createdAt") = date_trunc('month', now() - interval '5 months') then 1 end) as lastmonth5,
  COUNT(case when date_trunc('month' , "createdAt") = date_trunc('month', now() - interval '6 months') then 1 end) as lastmonth6
FROM "user";

--한달동안 가입한 유져를 1주일마다 통계
SELECT
  COUNT(case when date_trunc('week' , "createdAt") = date_trunc('week', now())  then 1 end) as thisweek,
  COUNT(case when date_trunc('week' , "createdAt") = date_trunc('week', now() - interval '1 weeks') then 1 end) as lastweek1,
  COUNT(case when date_trunc('week' , "createdAt") = date_trunc('week', now() - interval '2 weeks') then 1 end) as lastweek2,
  COUNT(case when date_trunc('week' , "createdAt") = date_trunc('week', now() - interval '3 weeks') then 1 end) as lastweek3
FROM "user";

--최근 6일동안 방문자 통계
SELECT
  COUNT(case when date_trunc('day' , "lastLogin") = date_trunc('day', now())  then 1 end) as today,
  COUNT(case when date_trunc('day' , "lastLogin") = date_trunc('day', now() - interval '1 days') then 1 end) as yesterday1,
  COUNT(case when date_trunc('day' , "lastLogin") = date_trunc('day', now() - interval '2 days') then 1 end) as yesterday2,
  COUNT(case when date_trunc('day' , "lastLogin") = date_trunc('day', now() - interval '3 days') then 1 end) as yesterday3,
  COUNT(case when date_trunc('day' , "lastLogin") = date_trunc('day', now() - interval '4 days') then 1 end) as yesterday4,
  COUNT(case when date_trunc('day' , "lastLogin") = date_trunc('day', now() - interval '5 days') then 1 end) as yesterday5,
  COUNT(case when date_trunc('day' , "lastLogin") = date_trunc('day', now() - interval '6 days') then 1 end) as yesterday6
FROM "user";

--성별별 가입자 카운트
SELECT
  COUNT(case when gender='male' then 1 end) as male,
  COUNT(case when gender='female' then 1 end) as female
FROM "user";

--연령별 가입자
SELECT
  COUNT(case when date_trunc('decade' , "birthDate") = date_trunc('decade', now())  then 1 end) as years00,
  COUNT(case when date_trunc('decade' , "birthDate") = date_trunc('decade', now() - interval '1 decades') then 1 end) as years10,
  COUNT(case when date_trunc('decade' , "birthDate") = date_trunc('decade', now() - interval '2 decades') then 1 end) as years20,
  COUNT(case when date_trunc('decade' , "birthDate") = date_trunc('decade', now() - interval '3 decades') then 1 end) as years30,
  COUNT(case when date_trunc('decade' , "birthDate") = date_trunc('decade', now() - interval '4 decades') then 1 end) as years40,
  COUNT(case when date_trunc('decade' , "birthDate") = date_trunc('decade', now() - interval '5 decades') then 1 end) as years50,
  COUNT(case when date_trunc('decade' , "birthDate") = date_trunc('decade', now() - interval '6 decades') then 1 end) as years60,
  COUNT(case when date_trunc('decade' , "birthDate") = date_trunc('decade', now() - interval '7 decades') then 1 end) as years70,
  COUNT(case when date_trunc('decade' , "birthDate") = date_trunc('decade', now() - interval '8 decades') then 1 end) as years80,
  COUNT(case when date_trunc('decade' , "birthDate") = date_trunc('decade', now() - interval '9 decades') then 1 end) as years90
FROM "user";

--주간 방문 수
SELECT
  COUNT(case when date_trunc('week' , "lastLogin") = date_trunc('week', now())  then 1 end) as thisweek,
  COUNT(case when date_trunc('week' , "lastLogin") = date_trunc('week', now() - interval '1 weeks') then 1 end) as lastweek1,
  COUNT(case when date_trunc('week' , "lastLogin") = date_trunc('week', now() - interval '2 weeks') then 1 end) as lastweek2,
  COUNT(case when date_trunc('week' , "lastLogin") = date_trunc('week', now() - interval '3 weeks') then 1 end) as lastweek3
FROM "user";