Estas consultas proporcionan un desglose de cuántas cuentas de nuevos usuarios se han creado dentro del período de tiempo, excluyendo las cuentas preparadas y no activadas. Los totales se desglosan por número de registros cada semana/mes/año, un total acumulado para el período de tiempo seleccionado y un número total hasta la fecha que incluye todos los registros anteriores.
NB: Si se elimina un usuario, sus registros ya no aparecerán en la base de datos y, por lo tanto, ya no se incluirán en los resultados de la consulta. Esto puede generar variaciones al comparar ejecuciones recientes con anteriores.
Registros Semanales
-- [params]
-- date :start_date
-- date :end_date
WITH weekly_signups AS (
SELECT
to_char(date_trunc('week', created_at)::date,'YYYY-MM-DD') AS week,
COUNT(*) AS signups
FROM users
WHERE created_at::date BETWEEN :start_date::date AND :end_date::date
AND staged = false
AND active = true
GROUP BY week
),
all_to_date AS (
SELECT COUNT(*) AS previous
FROM users
WHERE created_at::date < :start_date
AND staged = false
AND active = true
)
SELECT
week AS "Inicio de Semana",
signups AS "Registros Semanales",
SUM(signups::int) OVER (ORDER BY week) AS "Total Acumulado",
(SUM(signups::int) OVER (ORDER BY week) + previous::int) AS "Total General"
FROM weekly_signups, all_to_date
ORDER BY week ASC
| Inicio de Semana |
Registros Semanales |
Total Acumulado |
Total General |
| 2023-07-31 |
98 |
98 |
45418 |
| 2023-08-07 |
141 |
239 |
45559 |
| 2023-08-14 |
129 |
368 |
45688 |
| 2023-08-21 |
126 |
494 |
45814 |
| 2023-08-28 |
138 |
632 |
45952 |
| 2023-09-04 |
53 |
685 |
46005 |
Registros Mensuales
-- [params]
-- date :start_date
-- date :end_date
WITH monthly_signups AS (
SELECT
date_trunc('month', created_at)::date AS month,
to_char(date_trunc('month', created_at)::date,'Month YYYY') AS month_display,
COUNT(*) AS signups
FROM users
WHERE created_at::date BETWEEN :start_date::date AND :end_date::date
AND staged = false
AND active = true
GROUP BY month, month_display
),
all_to_date AS (
SELECT COUNT(*) AS previous
FROM users
WHERE created_at::date < :start_date
AND staged = false
AND active = true
)
SELECT
month_display AS "Mes",
signups AS "Registros Mensuales",
SUM(signups::int) OVER (ORDER BY month) AS "Total Acumulado",
(SUM(signups::int) OVER (ORDER BY month) + previous::int) AS "Total General"
FROM monthly_signups, all_to_date
ORDER BY month
| Mes |
Registros Mensuales |
Total Acumulado |
Total General |
| June 2023 |
596 |
596 |
45307 |
| July 2023 |
517 |
1113 |
45307 |
| August 2023 |
583 |
1696 |
45890 |
| September 2023 |
102 |
1798 |
46005 |
Registros Anuales
-- [params]
-- date :start_date
-- date :end_date
WITH yearly_signups AS (
SELECT
to_char(date_trunc('year', created_at)::date,'YYYY') AS year,
COUNT(*) AS signups
FROM users
WHERE created_at::date BETWEEN :start_date::date AND :end_date::date
AND staged = false
AND active = true
GROUP BY year
),
all_to_date AS (
SELECT COUNT(*) AS previous
FROM users
WHERE created_at::date < :start_date
AND staged = false
AND active = true
)
SELECT
year AS "Año",
signups AS "Registros Anuales",
SUM(signups::int) OVER (ORDER BY year) AS "Total Acumulado",
(SUM(signups::int) OVER (ORDER BY year) + previous::int) AS "Total General"
FROM yearly_signups, all_to_date
ORDER BY year ASC
| Año |
Registros Anuales |
Total Acumulado |
Total General |
| 2019 |
3590 |
3590 |
23135 |
| 2020 |
4258 |
7848 |
27393 |
| 2021 |
5908 |
13756 |
33301 |
| 2022 |
7889 |
21645 |
41190 |
| 2023 |
4815 |
26460 |
46005 |
), pero creo que es bastante tolerante con el formato. Yo uso el formato de fecha del Reino Unido (por ejemplo, 21/01/2024) cuando lo uso yo mismo, pero cuando comparto ejemplos de capturas de pantalla, intento ceñirme a, por ejemplo, 2024-01-21, ya que es más universal.