SQL query for last created users of the past 30 days

support
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.

1 Like
3

Got it.

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

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.