CODEWITHSUNDEEP
sqlsql-serverpostgresql
oikonomiyaki
oikonomiyaki

Reputation: 7951

Using RANK with COUNT(DISTINCT) aggregation at the same time

I have a subquery that joins my customers and transactions table, aliased as jq. I want to create a ranking of each customer's purchases (transactions) by order timestamp (order_ts). So I did,

SELECT customer_id, 
       order_id, 
       order_ts, 
       RANK() OVER (PARTITION BY customer_id ORDER BY order_ts ASC) AS purchase_rank, 
       amount 
FROM jq GROUP BY customer_id 
        ORDER BY customer_id;

Alongside with the purchase_rank column, I also want to know how many total purchases the customer have done. So this becomes:

SELECT customer_id, 
       order_id, 
       order_ts, 
       RANK() OVER (PARTITION BY customer_id ORDER BY order_ts ASC) AS purchase_rank, 
       // total purchases of this customer, counted by order_id
       amount 
FROM jq GROUP BY customer_id 
        ORDER BY customer_id;

Some order_ids are duplicated, so I want to count distinctly. How do I do this in MS SQL Server and Postgres without joining to a subquery?

Upvotes: 1

Views: 2023

Answers (1)

Squirrel
Squirrel

Reputation: 24803

Microsoft SQL Server

SELECT  customer_id,
    order_id,
    order_ts,
    purchase_rank,
    MAX(cnt) OVER(PARTITION BY customer_id) AS purchase_cnt,
    amount
FROM
(
    SELECT customer_id, 
           order_id, 
           order_ts, 
           RANK() OVER (PARTITION BY customer_id ORDER BY order_ts ASC) AS purchase_rank, 
           DENSE_RANK() OVER (PARTITION BY customer_id ORDER BY order_id ASC) AS cnt, 
           amount 
    FROM jq 
    -- GROUP BY customer_id 
) AS D  
ORDER BY customer_id;

Sorry, I am not familiar with postgresql.

Upvotes: 1

Related Questions