I have tables ‘users’ and ‘user_transactions’ in AWS Athena. The table has many columns but I am interested in 2 columns viz.
user name and
txn_type column has 3 possible values viz.
FT, NFT, PT.
For each user, I need to print the count of each transactions in the below format. The output should contain 4 columns with userId, and count of each txns.
User Name, CountOfTxns of type FT, CountOfTxns of type NFT, CountOfTxns of type PT
Morover, I need to join user and user_transactions table for printing records of users who are of type = ‘Gold’
so to find the users, it will be
select userId, userName from users, user_transactions t where u.userId = t=userId and u.type = 'Gold';
Then for these users, I need to print the output with 4 columns as described above.
So if a user with name = ‘ABC’ and Id = 1 and user_type = ‘Gold’ has 3 counts of FT, 5 counts of NFT and 0 counts of PT,
and if a user with name = ‘XYZ’ and Id = 2 and user_type = ‘Gold’ has 9 counts of FT, 0 counts of NFT and 45 counts of PT, the output should be the output should be
User Name, CountOfTxns of type FT, CountOfTxns of type NFT, CountOfTxns of type PT ABC, 3, 5, 0 XYZ, 9, 0, 45
How can I write a query which can print all counts in a single row?
You can calculate each field individually like so:
SELECT u.name, SUM(CASE WHEN t.txn_type = 'FT' THEN 1 ELSE 0 END) as FT_count, SUM(CASE WHEN t.txn_type = 'NFT' THEN 1 ELSE 0 END) as NFT_count, SUM(CASE WHEN t.txn_type = 'PT' THEN 1 ELSE 0 END) as PT_count FROM users u, transactions t WHERE u.user_id = t.user_id and u.type = 'Gold' GROUP BY u.name;