Advanced SQL with window function

Question

I have Table a(Dimension table) and Table B(Fact table) stores transaction shopper history.

Table a : shopped id(surrogate key) created for unique combination(any of column 2,colum3,column4 repeated it will have same shopper id)

Table b is transaction data.

I am trying to identify New customers and repeated customers for each week, expected output is below.

I am thinking following SQL Statement

Select COUNT(*) OVER (PARTITION BY shopperid,weekdate) as total_new_shopperid for Repeated customer, for Identifying new customer(ie unique) in same join condition, I am stuck on window function..

thanks,

Sam

If someone purchases twice in the first week, are they counted twice? — Gordon Linoff
– Gordon Linoff, Commented Mar 6, 2020 at 12:46
Gordon, if someone purchase twice it will be consider as a one — sam
– sam, Commented Mar 6, 2020 at 13:24

Popeye · Accepted Answer · 2020-03-06 05:12:22Z

1

You can use the DENSE_RANK analytical function along with aggregate function as follows:

SELECT WEEK_DATE, 
       COUNT(DISTINCT CASE WHEN DR = 1 THEN SHOPPER_ID END) AS TOTAL_NEW_CUSTOMER,
       SUM(CASE WHEN DR = 1 THEN AMOUNT END) AS TOTAL_NEW_CUSTOMER_AMT,
       COUNT(DISTINCT CASE WHEN DR > 1 THEN SHOPPER_ID END) AS TOTAL_REPEATED_CUSTOMER,
       SUM(CASE WHEN DR > 1 THEN AMOUNT END) AS TOTAL_REPEATED_CUSTOMER_AMT 
  FROM
      (
        select T.*, 
               DENSE_RANK() OVER (PARTITION BY SHOPPER_ID ORDER BY WEEK_DATE) AS DR
          FROM YOUR_TABLE T);
GROUP BY WEEK_DATE;

Cheers!!

answered Mar 6, 2020 at 5:12

Popeye

36k4 gold badges12 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

sam Over a year ago

Thank you very much, let me try to execute it today

Gordon Linoff · Accepted Answer · 2020-03-06 12:50:45Z

0

Tejash's answer is fine (and I'm upvoting it).

However, Oracle is quite efficient with aggregation, so two levels of aggregation might have better performance (depending on the data):

select week_date,
       sum(case when min_week_date = week_date then 1 else 0 end) as new_shoppers,
       sum(case when min_week_date = week_date then amount else 0 end) as new_shopper_amount,
       sum(case when min_week_date > week_date then 1 else 0 end) as returning_shoppers,
       sum(case when min_week_date > week_date then amount else 0 end) as returning_amount
from (select shopper_id, week_date,
             sum(amount) as amount,
             min(week_date) over (partition by shopper_id) as min_week_date
      from t
      group by shopper_id, week_date
     ) sw
group by week_date
order by week_date;

Note: If this has better performance, it is probably due to the elimination of count(distinct).

answered Mar 6, 2020 at 12:50

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

sam Over a year ago

Thank you very much, let me try this option

Collectives™ on Stack Overflow

Advanced SQL with window function

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related