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

No video

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

Рет қаралды 584

Күн бұрын

#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.

Пікірлер: 15

@karthikvijay5626 2 ай бұрын

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 2 ай бұрын

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 KZfaq.

@MeanLifeStudies Ай бұрын

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

@shitaldesai9139 Ай бұрын

@@MeanLifeStudies thanku sir very much