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

#education #sql #sqlinterview #dataengineers #dataanalytics #dataanalyst
#interviewquestion #sqlinterview #freshers #dataanalytics #datascience #datascientist
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.

[@karthikvijay5626]

I really love to solve SQL so i mostly learned and worked in MYSQL only , so if you make vedios in MYSQL it will be useful for me to gain MYSQL knowledge it's my request brother thankyou...

[@MeanLifeStudies]

Hi,
Due to some installation issues i couldn't use MySQL work bench. Remember these queries almost will works in MySQL as well. Just a few syntaxes and system defined functions will be different in MySQL and SQL Server.

[@dasubabuch1596]

Hi Sir, Thanks for uploaded the new video.
This is my query for this problem.
create table transactions (
user_id int,
transaction_date date,
amount int,
type varchar(10) check (type in ('credit', 'debit'))
);
insert into transactions values (1, to_date('2024-06-01','yyyy-mm-dd'), 1000, 'credit');
insert into transactions values (2, to_date('2024-06-02','yyyy-mm-dd'), 500, 'debit');
insert into transactions values (1, to_date('2024-06-03','yyyy-mm-dd'), 2000, 'credit');
insert into transactions values (3, to_date('2024-06-04','yyyy-mm-dd'), 1500, 'debit');
insert into transactions values (2, to_date('2024-06-05','yyyy-mm-dd'), 7500, 'credit');
insert into transactions values (4, to_date('2024-06-06','yyyy-mm-dd'), 3000, 'credit');
insert into transactions values (3, to_date('2024-06-07','yyyy-mm-dd'), 1000, 'debit');
insert into transactions values (4, to_date('2024-06-08','yyyy-mm-dd'), 2000, 'debit');
insert into transactions values (5, to_date('2024-06-09','yyyy-mm-dd'), 2500, 'credit');
insert into transactions values (5, to_date('2024-06-10','yyyy-mm-dd'), 500, 'debit');
insert into transactions values (1, to_date('2024-06-11','yyyy-mm-dd'), 4000, 'credit');
insert into transactions values (2, to_date('2024-06-12','yyyy-mm-dd'), 3000, 'debit');
insert into transactions values (3, to_date('2024-06-13','yyyy-mm-dd'), 1000, 'credit');
insert into transactions values (4, to_date('2024-06-14','yyyy-mm-dd'), 1500, 'debit');
insert into transactions values (5, to_date('2024-06-15','yyyy-mm-dd'), 2000, 'credit');
insert into transactions values (1, to_date('2024-06-16','yyyy-mm-dd'), 2500, 'debit');
insert into transactions values (2, to_date('2024-06-17','yyyy-mm-dd'), 2500, 'credit');
insert into transactions values (3, to_date('2024-06-18','yyyy-mm-dd'), 1500, 'debit');
insert into transactions values (4, to_date('2024-06-19','yyyy-mm-dd'), 1000, 'credit');
insert into transactions values (5, to_date('2024-06-20','yyyy-mm-dd'), 3000, 'credit');
*/
select * from transactions;
with ts
as
(
select user_id, transaction_Date, amount, type,
case when type = 'credit' then amount else -amount end as st
from transactions
),
t1 as
(
select user_id, transaction_Date, amount,type,
sum(st)over(partition by user_id order by transaction_date) as total
from ts
order by transaction_Date
)
select user_id, transaction_Date, amount,type,total,
case when type = 'credit' then 'eligible'
when type = 'debit' and total < 0 then 'ineligible' else 'eligible' end as eligibility
from t1;

[@MeanLifeStudies]

From next on wards please don't paste create and insert statements again. Only share your solution.

[@dasubabuch1596]

@@MeanLifeStudies Sure sir

[@AbhijitPaldeveloper]

In your table creation and insertion queries there is no transaction_id column. Please add correct queries

[@MeanLifeStudies]

Kindly excuse me. In a hurry, I might missed out right SQL statements to write over. I updated that Now. You can see now.

[@shitaldesai9139]

Hello sir help mi to solve this question
Write a query to display manager id who have 2 emplayee

[@MeanLifeStudies]

Join table with manager id = employee id and then assign cte.
Write again query to count of employees group by manager having count of employees is equal to 2

[@shitaldesai9139]

Sir will you upload this question on youtub

[@MeanLifeStudies]

Kindly excuse me it is a simple problem. Many wouldn't like these simple problems if I started uploading to UA-cam.

[@MeanLifeStudies]

Don't worry i will upload that and also adding another scenario to it.

[@shitaldesai9139]

@@MeanLifeStudies thanku sir very much