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
February 22, 2021 at 2:57 pm
This seems like homework. Can you post what you've tried, please?
--Jeff Moden
Change is inevitable... Change for the better is not.
February 22, 2021 at 3:10 pm
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
February 22, 2021 at 4:09 pm
-- 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
February 22, 2021 at 4:43 pm
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
February 22, 2021 at 8:12 pm
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.
February 22, 2021 at 8:31 pm
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
February 23, 2021 at 6:19 am
Thanks, the final script summed all required details.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply