CODEWITHSUNDEEP
sqlpostgresql
knoll
knoll

Reputation: 295

Two SELECT statements as two columns

This seems like a simple query, but I'm struggling with it.

Here's a sampling of my data.

user_id  dated
463      2016-01-01
463      2016-01-02
1456     2016-01-01
1456     2016-01-02
1398     2015-12-01
1398     2015-12-02

I want to get the number of unique users in two different time periods. Here are the queries I want to get a combined output from in a single row, and two columns.

-- 60
SELECT COUNT(DISTINCT(tld.user_id)) count_active_users_60
FROM table tld
WHERE tld.dated BETWEEN (NOW() - INTERVAL '60 days') AND (NOW() - INTERVAL '30 days')

-- 30
SELECT COUNT(DISTINCT(tld.user_id)) count_active_users_30
FROM table tld
WHERE tld.dated >= NOW() - INTERVAL '30 days'

I'd like an output that looks like this:

count_active_users_60    count_active_users_30
1                        2

I've been messing with various CASE statements, and sub-selects, but the distinct clause is throwing me off.

SELECT COUNT(DISTINCT(rar.user_id))
FROM 
(
SELECT user_id,
COUNT(CASE WHEN tld.dated BETWEEN (NOW() - INTERVAL '60 days') AND (NOW() - INTERVAL '30 days') THEN 1 ELSE NULL END) AS count_active_users_60,
COUNT(CASE WHEN tld.dated >= NOW() - INTERVAL '30 days' THEN 1 ELSE NULL END) AS count_active_users_30
FROM testing_login_duration tld
GROUP BY user_id
) rar;

Upvotes: 0

Views: 277

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

Use conditional aggregation:

SELECT COUNT(DISTINCT CASE WHEN tld.dated BETWEEN (NOW() - INTERVAL '60 days') AND (NOW() - INTERVAL '30 days')
                           THEN tld.user_id
                       END) count_active_users_60,
       COUNT(DISTINCT CASE WHEN tld.dated >= NOW() - INTERVAL '30 days'
                           THEN tld.user_id
                       END) count_active_users_30
FROM table tld
WHERE tld.dated >= NOW() - INTERVAL '60 days';

Upvotes: 3

Related Questions