Customer Transaction Table – CUSTOMER_TXNS Column Name Description Type Account_
ID: 3907860 • Letter: C
Question
Customer Transaction Table – CUSTOMER_TXNS
Column Name
Description
Type
Account_id
Account identifier
Integer
Txn_timestamp
Time of transaction (UTC)
Timestamp
Product_id
The id of the product purchased
Integer
Txn_Amt
The revenue amount of the transaction
Float
Txn_Qty
The number of items purchased
Integer
NOTE: Customer Transaction table has multiple records per account_id.
Customer Master Table – CUSTOMER_MSTR
Column Name
Description
Type
Account_id
Account identifier
Integer
Country
Country Code
Character(3)
Address
Address of the customer
Character(64)
Registerd_Dt
Date the account id was first used
Date
Tier
Account Tier
Integer
NOTE: Customer Master table has one record per account_id.
Q-Write a query to show the distribution of customers by the number of products they have purchased.
Column Name
Description
Type
Account_id
Account identifier
Integer
Txn_timestamp
Time of transaction (UTC)
Timestamp
Product_id
The id of the product purchased
Integer
Txn_Amt
The revenue amount of the transaction
Float
Txn_Qty
The number of items purchased
Integer
Explanation / Answer
SELECT
Account_id ,Product_id , Sum(Txn_Qty) as 'Total Qty' ,SUM(Txn_Amt) as 'Total Amt'
FROM
CUSTOMER_MSTR mst INNER JOIN CUSTOMER_TXNS txns ON mst.Account_id = txns.Account_id
GROUP BY Account_id ,Product_id
BOLD Capital words shows Keyword
itallic words are alias
Above query sample result
Discription: CUSTOMER_MSTR and CUSTOMER_TXNS having common column Account_id and CUSTOMER_TXNS having multiple recods for one Account_id hence CUSTOMER_TXNS is child table and CUSTOMER_MSTR parent table having one entry for each Account_id hence we inner join on Account_id. For getting details by Account_id and Product_id hence we use Group by Account_id and Product_id. One more reason for group by is to get sum of Txn_Qty and Txn_Amt for group of Account_id and Product_id. Whenever the combination of Account_id and Product_id changes, new row will be created in result.
Account_id Product_id Total Qty Total Amt 10001 PRD001 10 1050.00 10001 PRD002 80 4000.00 20001 PRD001 20 2100.00 30001 PRD004 20 3000.00 30001 PRD001 40 4200.00