SQL query for last created users of the past 30 days

Product Support

AI-generated summary

The discussion revolves around creating an SQL query to retrieve the last created users of the past 30 days. AquaL1te initially attempted to use a WITH clause to generate a date range, but encountered a syntax error.

AquaL1te shared the code:

WITH query_period AS (
SELECT
date_trunc('month', CURRENT_DATE) - INTERVAL '1 months' as period_start,
dat e_trunc('month', CURRENT_DATE) - INTERVAL '1 months' + INTERVAL '1 month' - INTERVAL '1 second' as period_end
),

SELECT username FROM users WHERE created_at >= period_start AND created_at <= period_end

AquaL1te received a PG::SyntaxError: ERROR: syntax error at or near "SELECT".

Pfaffman suggested looking into Data Explorer examples for inspiration.

AquaL1te eventually solved the issue with a simplified query:

SELECT username FROM users WHERE created_at >=  CURRENT_TIMESTAMP - INTERVAL '30 days' AND staged = false

This query successfully retrieves users created within the last 30 days.

1

How to get all users that signed up in the last 30 days? I suppose I need some builtin SQL function to generate this date format and then do -30 days. I’ve tried a few examples from the Interwebz, such as this, but I get a syntax error.

WITH query_period AS (
SELECT
date_trunc('month', CURRENT_DATE) - INTERVAL '1 months' as period_start,
date_trunc('month', CURRENT_DATE) - INTERVAL '1 months' + INTERVAL '1 month' - INTERVAL '1 second' as period_end
),

SELECT username FROM users WHERE created_at >= period_start AND created_at <= period_end

PG::SyntaxError: ERROR:  syntax error at or near "SELECT"
LINE 13: SELECT username FROM users WHERE created_at >= period_start ...

Does anyone see what I’m missing here? Or could this be done much better as a whole?

2

You might have a look in Topics tagged data-explorer or Discourse Data Explorer or What cool data explorer queries have you come up with? for some examples.

3

Got it.

SELECT username FROM users WHERE created_at >= CURRENT_TIMESTAMP - INTERVAL '30 days' AND staged = false