Question 89601 in PostgreSQL Question posted in
The official documentation can be found here.

Calculate cumulative distinct – Postgresql

I have on the datebase a table containing:

  • created_at
  • order_id
  • customer_id

I want to calculate the cumulative distinct number of customers per day.

I wrote this query

SELECT
    created_at::date,
    COUNT(DISTINCT customer_id) OVER (ORDER BY created_at::date) as cumulative_users
FROM orders
GROUP BY 1
ORDER BY 1

then I figured out that PostgreSQL doesn’t support distinct in a window function.

Can you please help me writing this code?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0 
    WITH firstseen AS (
    SELECT
        customer_id,
        MIN(created_at) created_at
    FROM orders
    GROUP BY 1
)
SELECT
    DISTINCT created_at :: date,
    COUNT(customer_id) OVER (ORDER BY created_at :: date) daily_cumulative_count
FROM firstseen
ORDER BY 1

  2. 0

    No windowing function needed here imho.

    select created_at , count(distinct customer_id) from order_table group by created_at;

    See SQLFiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search