Weekly/Monthly/Yearly Signups

data & reporting
1

:memo: These queries give a breakdown of how many new user accounts have been created within the timeframe, excluding staged and non-activated accounts. The totals are broken down by number of signups each week/month/year, a running total for the time period selected, and a total number to date which includes all previous signups.

NB: If a user is deleted their records no longer appear in the database and will therefore no longer be included in the query results. This can lead to variations when comparing recent runs against previous ones.

Weekly Signups

-- [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 "Week Begin", 
    signups AS "Weekly Signups", 
    SUM(signups::int) OVER (ORDER BY week) AS "Running Total",
    (SUM(signups::int) OVER (ORDER BY week) + previous::int) AS "Sum Total"
FROM weekly_signups, all_to_date
ORDER BY week ASC
Week Begin Weekly Signups Running Total Sum Total
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

Monthly Signups

-- [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 "Month", 
    signups AS "Monthly Signups", 
    SUM(signups::int) OVER (ORDER BY month) AS "Running Total",
    (SUM(signups::int) OVER (ORDER BY month) + previous::int) AS "Sum Total"
FROM monthly_signups, all_to_date
ORDER BY month
Month Monthly Signups Running Total Sum Total
June 2023 596 596 44790
July 2023 517 1113 45307
August 2023 583 1696 45890
September 2023 102 1798 46005

Yearly Signups

-- [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 "Year", 
    signups AS "Yearly Signups", 
    SUM(signups::int) OVER (ORDER BY year) AS "Running Total",
    (SUM(signups::int) OVER (ORDER BY year) + previous::int) AS "Sum Total"
FROM yearly_signups, all_to_date
ORDER BY year ASC
Year Yearly Signups Running Total Sum Total
2019 3590 3590 23135
2020 4258 7848 27393
2021 5908 13756 33301
2022 7889 21645 41190
2023 4815 26460 46005
8 Likes
3

Hi, apologies for being so obtuse about how this works, but what does one of these examples look like when the dates are filled in?

4

I’m not sure what you mean? There’s an example of the results underneath each, if that’s what you’re looking for?

1 Like
5

Understood.

Here’s what I’m trying to ask: Where in the code itself do I put my start and end date, and in what format do I write it?

6

You don’t, as it should add input boxes above the button to run the query (if not, refresh after saving and they’ll show up), then

I think it’ll just give you a date picker.

2 Likes
7

Ah, yes. There’s a known issue where the parameter input boxes don’t show up the first time until after a page refresh.

It’s not quite swish enough yet to have a date picker (though here’s hoping :slight_smile: :crossed_fingers:), but I think it’s quite forgiving on format. I use the UK date format (eg 21/01/2024) when I’m using it myself, but when I share screenshot examples I try and stick to eg 2024-01-21 as it’s more universal.

IMG_3563
IMG_35631179×1540 146 KB

3 Likes
8

Got it now. Thanks. Needed the page refresh.

1 Like