Create table PURCHASE
(
TRANSACTION_ID varchar(10),
USER_ID varchar(10),
COUNTRY varchar(50),
PRICE float,
TIMES DATETIME
)
Create table CURRENCY
(
TIMET DATETIME,
COUNTRY varchar(50),
US_PRICE float
)
INSERT INTO PURCHASE
VALUES('A0101', 'ABC', 'CANADA' , 5.55, '2021-01-01 10:00:00'),
('A0102', 'DFG', 'MEXICO' , 2.66, '2021-01-01 10:00:00'),
('A0103', 'ABC', 'CANADA' , 8.35, '2021-01-02 10:00:00'),
('A0104', 'HIJ', 'BRAZIL' , 2.35, '2021-01-04 10:00:00'),
('A0105', 'KLM', 'ITALY' , 9.15, '2021-01-05 10:00:00'),
('A0106', 'NOP', 'INDIA' , 2.99, '2021-01-06 10:00:00'),
('A0108', 'NOP', 'INDIA' , 0.99, '2022-01-06 10:00:00'),
('A0101', 'JKE', 'CANADA' , 5.55, '2021-01-01 10:00:00'),
('A0102', 'POP', 'MEXICO' , 2.66, '2021-01-01 10:00:00'),
('A0103', 'BOB', 'CANADA' , 8.35, '2021-01-02 10:00:00'),
('A0104', 'KPT', 'BRAZIL' , 2.35, '2021-01-04 10:00:00'),
('A0105', 'RRT', 'ITALY' , 9.15, '2021-01-05 10:00:00'),
('A0106', 'IIO', 'INDIA' , 2.99, '2021-01-06 10:00:00'),
('A0108', 'XXP', 'INDIA' , 0.99, '2022-01-06 10:00:00'),
('A0109', 'ABC', 'CANADA' , 5.55, '2021-02-01 10:00:00'),
('A01010', 'DFG', 'MEXICO' , 2.66, '2021-02-01 10:00:00'),
('A01011', 'ABC', 'CANADA' , 8.35, '2021-02-02 10:00:00'),
('A01012', 'ABC', 'CANADA' , 5.55, '2021-02-01 10:00:00'),
('A01023', 'DFG', 'MEXICO' , 2.66, '2021-02-01 10:00:00'),
('A01034', 'ABC', 'CANADA' , 8.35, '2021-02-02 10:00:00'),
('A01045', 'HIJ', 'BRAZIL' , 2.35, '2021-02-04 10:00:00'),
('A01056', 'KLM', 'ITALY' , 9.15, '2021-02-05 10:00:00'),
('A01067', 'NOP', 'INDIA' , 2.99, '2021-02-06 10:00:00'),
('A01088', 'NOP', 'INDIA' , 0.99, '2022-02-06 10:00:00'),
('A01099', 'ABC', 'CANADA' , 5.55, '2021-02-01 10:00:00'),
('A01015', 'DFG', 'MEXICO' , 2.66, '2021-02-01 10:00:00'),
('A01016', 'ABC', 'CANADA' , 8.35, '2021-02-02 10:00:00')
INSERT INTO CURRENCY
VALUES('2021-01-01','CANADA','0.2'),
('2021-01-02','CaNadA','0.25'),
('2021-01-02','CanadA','0.35'),
('2021-01-02','CaNadA','0.3'),
('2021-01-03','CanadA','0.4'),
('2021-01-04','CANADA','0.5'),
('2021-01-05','CanadA','0.6'),
('2021-01-06','CANADA','0.7'),
('2021-01-07','CanadA','0.35'),
('2021-01-08','CaNadA','0.45'),
('2021-02-01','CANADA','0.2'),
('2021-02-02','CaNadA','0.25'),
('2021-02-02','CanadA','0.35'),
('2021-02-02','CaNadA','0.3'),
('2021-02-03','CanadA','0.4'),
('2021-02-04','CANADA','0.5'),
('2021-02-05','CanadA','0.6'),
('2021-02-06','CANADA','0.7'),
('2021-02-07','CanadA','0.35'),
('2021-02-08','CaNadA','0.45'),
('2021-01-01','MExIcO','0.3'),
('2021-01-01','MExIcO','0.32'),
('2021-01-02','MExIcO','0.33'),
('2021-01-03','MExIcO','0.34'),
('2021-01-04','MExIcO','0.35'),
('2021-01-05','MExIcO','0.6'),
('2021-02-01','MExIcO','0.3'),
('2021-02-01','MExIcO','0.32'),
('2021-02-02','MExIcO','0.33'),
('2021-02-03','MExIcO','0.34'),
('2021-02-04','MExIcO','0.35'),
('2021-02-05','MExIcO','0.6'),
('2021-01-01','BRAZIL','0.15'),
('2021-01-01','bRazil','0.17'),
('2021-01-02','BRaZil','0.18'),
('2021-01-03','BRAZIL','0.19'),
('2021-01-04','bRazil','0.20'),
('2021-01-04','BRAZIL','0.15'),
('2021-02-01','BRAZIL','0.15'),
('2021-02-01','bRazil','0.17'),
('2021-02-02','BRaZil','0.18'),
('2021-02-03','BRAZIL','0.19'),
('2021-02-04','bRazil','0.20'),
('2021-02-04','BRAZIL','0.15'),
('2021-01-01','italY','0.15'),
('2021-01-01','ITALY','0.17'),
('2021-01-02','ItalY','0.19'),
('2021-01-03','italY','0.18'),
('2021-01-04','ITALY','0.17'),
('2021-01-05','italY','0.15'),
('2021-02-01','italY','0.15'),
('2021-02-01','ITALY','0.17'),
('2021-02-02','ItalY','0.19'),
('2021-02-03','italY','0.18'),
('2021-02-04','ITALY','0.17'),
('2021-02-05','italY','0.15'),
('2021-01-01','INDIA','0.15'),
('2022-01-02','INDIA','0.45'),
('2021-01-03','iNDIa','0.15'),
('2022-01-04','INDIA','0.45'),
('2021-01-04','iNDIa','0.15'),
('2022-01-05','INDIA','0.45'),
('2021-02-01','INDIA','0.15'),
('2022-02-02','INDIA','0.45'),
('2021-02-03','iNDIa','0.15'),
('2022-02-04','INDIA','0.45'),
('2021-02-04','iNDIa','0.15'),
('2022-02-05','INDIA','0.45')
GO
/*PURCHASE_TABLE
TRANSACTION_ID USER_ID COUNTRY PRICE DATE
A0101 ABC USA 2.35 2021-01-01 10:00:00
A0101 CDE CANADA 1.35 2021-04-01 11:00:00
A0102 ABC BRAZIL 2.55 2021-03-01 10:30:00
A0103 FGH CANADA 6.00 2021-01-01 12:00:00
A0104 IJK INDIA 1.95 2021-07-01 10:50:00
CURRENCY_TABLE
DATE COUNTRY USD PRICE
2021-01-01 CANADA 0.5
2021-01-01 CANAdA 0.7
2021-03-01 BRAZil 0.2
2021-07-01 IndIa 0.9
histogram of average monthly usd order spend per 10% group of customer, when the customers are groped in the decreasing order of their CY.
When have two different price for the same day use the highst price.
#Getting the percentage of users
SELECT USER_ID,
count(*) * 100.0 / (SELECT count(*) from PURCHASE) as 'User Percentage'
FROM PURCHASE
GROUP BY USER_ID
#Getting the Sum of CY*USD using the max US_PRICE per day.
SELECT
P.USER_ID,
MONTH(TIMES),
COUNT(USER_ID),
SUM(P.PRICE * C.US_PRICE) AS SUM_USD_PRICE
FROM PURCHASE AS P
INNER JOIN CURRENCY AS C
ON UPPER(P.COUNTRY) = UPPER(C.COUNTRY) AND CAST(P.TIMES AS DATE) = C.TIMET
WHERE C.US_PRICE IN (
SELECT MAX(US_PRICE)
FROM CURRENCY
GROUP BY TIMET, COUNTRY
)
AND YEAR(TIMES) = 2021
GROUP BY P.USER_ID, MONTH(TIMES)
#Creating % clusters
select
USER_ID,
SumPrice,
AVG_SUM,
MONTH,
User_Percentage_FORMAT,
case
when User_Percentage between 1 and 10 then '0 to 10%'
when User_Percentage between 11 and 20 then '11 to 20%'
when User_Percentage between 21 and 30 then '21 to 30%'
when User_Percentage between 31 and 40 then '31 to 40%'
when User_Percentage between 41 and 50 then '41 to 50%'
when User_Percentage between 51 and 60 then '51 to 60%'
when User_Percentage between 61 and 70 then '61 to 70%'
when User_Percentage between 71 and 80 then '71 to 80%'
when User_Percentage between 81 and 90 then '81 to 90%'
when User_Percentage between 91 and 100 then '91 to 100%'
end as percentage_group
FROM SumPrice_USER
WHERE User_Percentage <= 10
*/
-- Create a histogram of average monthly usd order spend per 10% group of customer, when the customers are groped in the decreasing order of their CY.
-- When have two different price for the same day use the highst price.
WITH SumPrice_USER
AS( SELECT
USER_ID,
SUM(P2.PRICE * C.US_PRICE) AS SumPrice,
P2.TIMES as TS,
MONTH(P2.TIMES) AS MONTH,
AVG(SUM(P2.PRICE * C.US_PRICE)) OVER (PARTITION BY MONTH(P2.TIMES)) avg_sum,
count(USER_ID) * 100.0 / sum(count(USER_ID)) Over(PARTITION BY MONTH(P2.TIMES)) as 'User_Percentage',
concat(format(count(USER_ID) * 100.0 / sum(count(USER_ID)) Over(PARTITION BY MONTH(P2.TIMES)),'0.##') ,'%') as 'User_Percentage_FORMAT'
FROM
PURCHASE P2
INNER JOIN CURRENCY AS C
ON UPPER(P2.COUNTRY) = UPPER(C.COUNTRY) AND CAST(P2.TIMES AS DATE) = C.TIMET
WHERE C.US_PRICE IN (
SELECT MAX(US_PRICE)
FROM CURRENCY
GROUP BY TIMET, COUNTRY
)
AND YEAR(P2.TIMES) = '2021'
GROUP BY USER_ID,TIMES,MONTH(TIMES)
)
/*select
USER_ID,
SumPrice,
AVG_SUM,
MONTH,
User_Percentage_FORMAT
FROM SumPrice_USER
-- WHERE User_Percentage <= 10
order by month asc, SumPrice desc*/
select
USER_ID,
SumPrice,
AVG_SUM,
MONTH,
User_Percentage_FORMAT,
case
when User_Percentage between 1 and 10 then '0 to 10%'
when User_Percentage between 11 and 20 then '11 to 20%'
when User_Percentage between 21 and 30 then '21 to 30%'
when User_Percentage between 31 and 40 then '31 to 40%'
when User_Percentage between 41 and 50 then '41 to 50%'
when User_Percentage between 51 and 60 then '51 to 60%'
when User_Percentage between 61 and 70 then '61 to 70%'
when User_Percentage between 71 and 80 then '71 to 80%'
when User_Percentage between 81 and 90 then '81 to 90%'
when User_Percentage between 91 and 100 then '91 to 100%'
end as percentage_group
FROM SumPrice_USER
WHERE User_Percentage <= 10
Telegram: DB developers channel