August 17, 2022 at 5:48 am
Good Morning,
I have the two tables customers (joined) and their orders (dateoforder- record perday)
CREATE TABLE Customers (
CID int NOT NULL PRIMARY KEY,
GroupName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CustomerStatus SMALLINT, ---1 or 0
DateofJoin DATE
);
INSERT INTO Customers Values(100, 'ABC123', 'John',30,1,'01/14/2022')
INSERT INTO Customers Values(101, 'ABC123', 'King',40,0,'01/19/2022')
INSERT INTO Customers Values(102, 'XYZ456', 'SARA',55,1,'01/21/2022')
INSERT INTO Customers Values(103, 'ABC123', 'Diva',23,1,'02/04/2022')
INSERT INTO Customers Values(104, 'DEF123', 'Klip',19,1,'02/17/2022')
INSERT INTO Customers Values(105, 'ABC456', 'BobE',45,1,'02/27/2022')
INSERT INTO Customers Values(106, 'ABC123', 'MIla',20,1,'03/01/2022')
INSERT INTO Customers Values(107, 'DEF123', 'Yore',19,1,'03/01/2022')
INSERT INTO Customers Values(108, 'ABC456', 'Hams',21,1,'03/11/2022')
INSERT INTO Customers Values(109, 'ABC123', 'Zand',33,1,'04/04/2022')
INSERT INTO Customers Values(110, 'ABC123', 'Loda',44,0,'04/04/2022')
INSERT INTO Customers Values(111, 'DEF456', 'Mopa',19,1,'04/04/2022')
INSERT INTO Customers Values(112, 'DEF456', 'Kodi',21,1,'04/24/2022')
CREATE TABLE Orders (
CID int NOT NULL,
DateofOrder DATE,
OrderCretedIND SMALLINT , ---1 and0 values only :- 1 means placed order on that day 0 means no order. (true or false)
MoneyDepositedInd SMALLINT, --- 1s or 0s 1 means active 0 means non active
MoneyWithdrawalsIND SMALLINT, ---1s and 0 (true or false)
OrderedAmount BIGINT
);
INSERT INTO Orders Values(100 ,'01/14/2022',1,1,0,6500)
INSERT INTO Orders Values(100 ,'02/04/2022',0,1,0,NULL)
INSERT INTO Orders Values(100 ,'05/16/2022',1,1,0,1500)
INSERT INTO Orders Values(100 ,'06/14/2022',0,1,0,NULL)
INSERT INTO Orders Values(100 ,'07/23/2022',1,1,0,500)
INSERT INTO Orders Values(100 ,'08/01/2022',1,1,0,600)
INSERT INTO Orders Values(101 ,'01/21/2022',1,1,0,100)
INSERT INTO Orders Values(101 ,'01/23/2022',1,1,0,520)
INSERT INTO Orders Values(101 ,'03/01/2022',1,1,0,660)
INSERT INTO Orders Values(103 ,'01/22/2022',1,1,0,230)
INSERT INTO Orders Values(103 ,'01/22/2022',1,1,0,240)
INSERT INTO Orders Values(103 ,'01/22/2022',1,1,0,250)
INSERT INTO Orders Values(103 ,'01/23/2022',1,1,0,10)
INSERT INTO Orders Values(103 ,'01/24/2022',1,1,0,60)
INSERT INTO Orders Values(103 ,'01/25/2022',1,1,0,500)
INSERT INTO Orders Values(103 ,'01/26/2022',1,1,0,200)
INSERT INTO Orders Values(103 ,'01/27/2022',1,1,0,300)
INSERT INTO Orders Values(103 ,'01/28/2022',1,1,0,500)
INSERT INTO Orders Values(103 ,'05/16/2022',1,1,0,700)
INSERT INTO Orders Values(103 ,'07/23/2022',1,1,0,100)
INSERT INTO Orders Values(103 ,'08/01/2022',1,1,0,100)
I am trying to see two things here
1) for the month of Jan joined customers (or each month opened accounts) how many were uniquely order created (OrderCretedIND=1) each month ( DateofOrder).
2) How many months did they average order created/placed/active (OrderCretedIND=1).
Please help with it.
Thank you,
ASita
August 18, 2022 at 6:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply