SQL Interview Question - Solution (Part - XII) | Data Analyst

No video

SQL Interview Question - Solution (Part - XII) | Data Analyst | Data Engineer

Рет қаралды 583

Күн бұрын

#education #sql #sqlinterview #dataengineers #dataanalytics #dataanalyst
#interviewquestion #sqlinterview
Here are table creation and insertion queries:
------------------------------------------------------------------------
create table transactions (
user_id int,
transaction_date date,
Transaction_Id int primary key,
amount int,
type varchar(10) check (type in ('credit', 'debit'))
);
insert into transactions values
(1, '2024-06-01', 10,1000, 'credit'),
(2, '2024-06-02', 18,500, 'debit'),
(1, '2024-06-03', 22,2000, 'credit'),
(3, '2024-06-04', 33,1500, 'debit'),
(2, '2024-06-05', 45,7500, 'credit'),
(4, '2024-06-06', 55,3000, 'credit'),
(3, '2024-06-07', 58,1000, 'debit'),
(4, '2024-06-08', 65,2000, 'debit'),
(5, '2024-06-09', 72,2500, 'credit'),
(5, '2024-06-10', 78,500, 'debit'),
(1, '2024-06-11', 80,4000, 'credit'),
(2, '2024-06-12', 89,3000, 'debit'),
(3, '2024-06-13', 90,1000, 'credit'),
(4, '2024-06-14', 101,1500, 'debit'),
(5, '2024-06-15', 125,2000, 'credit'),
(1, '2024-06-16', 150,2500, 'debit'),
(2, '2024-06-17', 164,2500, 'credit'),
(3, '2024-06-18', 180,1500, 'debit'),
(4, '2024-06-19', 187,1000, 'credit'),
(5, '2024-06-20', 198,3000, 'credit')
Q 17) Return the eligibility of transactions done by each user.

Пікірлер: 6

@kailashpatro5768 Ай бұрын

hi sir this is my solution select user_id, count(1) as total_count , count(case when type = 'credit' then 1 end)*100/count(*) as credit_percentage, count(case when type = 'debit' then 1 end)*100/count(*) as debit_percentage from transactions12 group by user_id

@dasubabuch1596 Ай бұрын

Hi Sir, This is my query with t as ( select count(*) as total_txns, user_id from transactions group by user_id ), t1 as ( select user_id, type, count(type)over(partition by user_id, type order by user_id) as count_of_each_type from transactions ), t2 as ( select t1.user_id, t1.type,t.total_txns,t1.count_of_each_type, case when type = 'credit' then (t1.count_of_each_type/t.total_txns)*100 when type = 'debit' then (t1.count_of_each_type/t.total_txns)*100 end as percent from t1 inner join t on t.user_id = t1.user_id ), t3 as ( select user_id, total_txns, count(case when type = 'credit' then percent end) as credit_txns, count(case when type = 'debit' then percent end) as debit_txns from t2 group by user_id, total_txns ) select user_id, total_Txns, (credit_txns/total_Txns)*100 as Credit_Percent, (debit_txns/total_Txns)*100 as debit_Percent from t3;

@AbhijitPaldeveloper Ай бұрын

My Solution in Mysql: SELECT user_id, total_number, round((total_credit_count*100/total_number),0) as credit_percent, round((total_debit_count*100/total_number),0) as debit_percent FROM(SELECT user_id, COUNT(user_id) as total_number, SUM(CASE WHEN type='credit' THEN 1 ELSE 0 END) as total_credit_count, SUM(CASE WHEN type='debit' THEN 1 ELSE 0 END) as total_debit_count FROM `transactions` GROUP BY user_id) as x;