How do you partition results weekly in SQL?

Question

I have to find the 3 highest spending customers from

Customer

(customer name, id) and

order

(order id, order amt, order date) for every week. If I run the query today, it should show the top 3 for all weeks for which order date exists.

I am thinking about doing a

Partition by

over the date (weekly), but I can't find any method to do that? Has anyone done a weekly partition of results?

I know it's not right, but this is what I have:

Select Top 3 customer_name, id OVER (partition by [week])

(

    Select c.customer_name, c.id, o.order_amt,

    from customer c

    Join Order o

        on c.id=o.id

    group by c.id 

)

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

According to your table structure, where orders.order_id is customer.id use this statement

select
    *
from
(
    select
        details.*
        ,dense_rank() over (partition by week_num order by order_amt desc) as rank_num
    from
    (
        select
            c.id as customer_id
            ,c.name
            ,sum(o.order_amt) as order_amt
            ,datepart(WEEK,o.order_date) as week_num    
        from customer c
        join orders o on c.id=o.order_id
        group by c.id,c.name,datepart(WEEK,o.order_date)
    )details

)dets 
where dets.rank_num<=3

Updated : changed statement to use just 2 tables

How do you partition results weekly in SQL?

Sign up