postgresql 한달동안 방문자, 최근 6일동안 방문자, 전체 가입자 수 (월간) 통계

--한달동안 가입한 유져를 1주일마다 통계
SELECT date_trunc('week', "createdAt") AS "week" , count(*), 0 AS "users"
FROM "user"
WHERE "createdAt" > now() - interval '1 months'
GROUP BY 1
ORDER BY 1;

--주간 방문 수
SELECT date_trunc('week', "lastLogin") AS "week" , count(*) AS "users"
FROM "user"
WHERE "lastLogin" > now() - interval '1 months'
GROUP BY 1
ORDER BY 1;

--최근 6일동안 방문자 통계
SELECT date_trunc('day', "lastLogin") AS "day" , count(*) AS "users"
FROM "user"
WHERE "lastLogin" > now() - interval '1 weeks'
GROUP BY 1
ORDER BY 1;

--전체 가입자 수 (월간)
SELECT date_trunc('month', "createdAt") AS "month" , count(*) AS "users"
FROM "user"
WHERE "createdAt" > now() - interval '1 years'
GROUP BY 1
ORDER BY 1;

--연령별 가입자
SELECT date_trunc('decade', "birthDate") AS "decade" , count(*) AS "users"
FROM "user"
WHERE "birthDate" > now() - interval '100 years'
GROUP BY 1
ORDER BY 1;

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

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