Getting Total Spending/Purchase of Unique Customers against Each Store - TSQL

  • I have a requirement where purchases (spending) of customers made at different stores are provided in transactions table. It is required that

    What are the total purchases made at each store.

    How many unique/distinct customers made purchases at each store.

    What are the total purchase of those unique customer at all stores. What are the total purchases of unique customer is the tricky part. The query gives the total of purchase for each customer at each store. There are 3 stores where unique customer are different. There are 3,4,2 unique customers for each store. We need to plot the total purchases of customer against only those store where they exists. First store will have total purchases of only those 3 customers who make purchases there and second one only have total of those 4 who shopped there. I hope my point is clear.

    Challenge I am facing is that how to attain the 03rd requirement. Did some manual work in excel to display the desired results.

    Below is the Table Creation Script:

    CREATE TABLE [dbo].[Transactions](
    [Date] [datetime] NULL,
    [Cust_ID] [smallint] NULL,
    [Amount] [smallint] NULL,
    [Store] [varchar](10) NULL
    )
    GO

    INSERT INTO Transactions (Date,Cust_ID,Amount,Store)
    Values
    ('20210222','1001',100,'Mega Store'),
    ('20210223','1002',200,'Z Trade'),
    ('20210224','1003',300,'Alpine'),
    ('20210227','1002',200,'Alpine'),
    ('20210228','1003',300,'Mega Store'),
    ('20210302','1001',100,'Alpine'),
    ('20210303','1002',200,'Mega Store'),
    ('20210304','1003',300,'Z Trade'),
    ('20210306','1001',100,'Mega Store'),
    ('20210307','1002',200,'Z Trade'),
    ('20210308','1003',300,'Alpine'),
    ('20210309','1004',400,'Mega Store')

    select * from Transactions

    • This topic was modified 1 week, 2 days ago by  Rehan Ahmad.
    • This topic was modified 1 week, 2 days ago by  Rehan Ahmad.
    Attachments:
    You must be logged in to view attached files.
  • This seems like homework.  Can you post what you've tried, please?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Dear, not homework. I done a lot and tried by using joins but stuck on the final result where total purchase of each customer required to be plotted (against those stores where he made purchases). Rest achieved through below code:

    -- Get Total Spend and Distinct Customer --
    SELECT A.Store,SUM(A.Amount) AS Spend,B.Distinct_Customer
    from Transactions A
    left outer join
    (select store,count(distinct cust_id) as Distinct_Customer from Transactions group by Store) B
    on A.Store = B.store
    group by a.store,b.Distinct_Customer

    -- Store wise Unique Cust_ids

    select distinct store,cust_id from Transactions
    group by store,cust_id
  • -- What are the total purchases made at each store.
    select store,SUM(Amount)
    from Transactions
    group by store

    -- How many unique/distinct customers made purchases at each store.
    select store,COUNT(distinct a.Cust_ID)
    from Transactions a
    group by store

    -- What are the total purchase of those unique customer at all stores.
    select a.store,a.Cust_ID,SUM(Amount) CustomerTotaAtEachStore
    from Transactions a
    group by a.store, a.cust_id

    -- What are the total purchase of those unique customer at all stores.
    select a.Cust_ID,SUM(Amount) CustomerTota
    from Transactions a
    group by a.cust_id
  • Thanks Jonathan,

    What are the total purchases of unique customer is the tricky part. The query gives the total of purchase for each customer at each store. There are 3 stores where unique customer are different. There are 3,4,2 unique customers for each store. We need to plot the total purchases of customer against only those store where they exists. First store will have total purchases of only those 3 customers who make purchases there and second one only have total of those 4 who shopped there. I hope my point is clear. Further, below is my final attempt for the solution:

    select store,sum(total_spend) from (
    select distinct a.store,a.cust_id,b.total_spend from Transactions a
    left outer join
    (select cust_id,sum(amount) as total_spend from Transactions
    group by cust_id) b
    on a.cust_id = b.cust_id
    --order by store,cust_id
    ) t
    group by store

     

    • This reply was modified 1 week, 2 days ago by  Rehan Ahmad.
  • I don't think I understand what you want.

    This query is one of the above with an order by added

    -- What are the total purchase of those unique customer at all stores.
    select a.store,a.Cust_ID,SUM(Amount) CustomerTotaAtEachStore
    from Transactions a
    group by a.store, a.cust_id
    order by 1,2

    If it's not what you want please provide a list of the output you expect.

  • Output required mentioned in the attachments of first post in Table (4) where last column obtained with the query mentioned in my last reply. I will join the ones provided by your goodself and that one. Thus Objective will be achieved. Thanks for your post which motivated me.

  • select store, SUM(Amount) [Total Purchase],COUNT(distinct a.Cust_ID) [Distinct Customer], z.x [Total Purchase of Distinct Customer]
    from Transactions a
    cross apply(select sum(Amount) x
    from Transactions b
    where exists(select *
    from Transactions c
    where c.Store = a.Store
    and c.Cust_ID = b.Cust_ID)) z
    group by store,z.x
    order by 1
  • Thanks, the final script summed all required details.

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply