Hi Ankit, I’ve been following your channel since a long time and I’ve practiced almost every SQL question that you’ve uploaded. Last month I had a Data Engineer interview with Amazon, they asked me 5 SQL questions and you have uploaded 4 of them! Next Monday will be my first day at Amazon :) Thank you so much! I’m truly grateful to you! Hope you achieve everything that you want in life.

@jparmar1

Жыл бұрын

Apna interview experience bataiye na. Data structures and algos bhi they ?

@HarishKumar-sh3bn

Жыл бұрын

Where you learned big data / data engineering?

@ankitbansal6

Жыл бұрын

Thats brilliant. Keep shining 🌟🌟

@khushahmed97

Жыл бұрын

It’ll be helpful if you share your experience , or your linkedin id

@ankit9531

Жыл бұрын

Same comment everywhere from linkedin to youtube 🤣🤣

Hi Ankit hats off to your work amazing topics and questions you are covering. Very helpful channel. Thankyou for your KZread videos.

Thanks for introducing Data Lamur to us

Really masterclass Ankit!

insightful solution

My Approach to the question (using concepts learned in your earlier videos): with cte as ( select transaction_id, type, to_date(transaction_date) transaction_date, sum(case when type='withdrawal' then amount*-1 else amount end) over (order by transaction_date rows between unbounded preceding and current row) as run_sum, row_number() over (partition by to_date(transaction_date) order by (select null)) as rn from transaction44 --order by transaction_date ) ,cte2 as ( select transaction_date, transaction_id, last_value(run_sum) over (partition by transaction_date order by rn) amt, run_sum From cte ) select transaction_date, transaction_id,amt from cte2 where amt=run_sum;

Great explanation sir 💯 Thanks a lot sir 🙏

Awesome vdo ❤❤😇

wow !!! Gifted to find like solution from you.we all learn together .waiting for many many more !!

@ankitbansal6

Жыл бұрын

Glad to hear that

Thank you !

Sir please also add videos in database design playlist because that is also the most important part of interview along with sql

Ankit bhai superb explanation. I am waiting for your python playlist. Kindly post videos regarding that playlist as well

Very good

you are doing a noble work

@rohansrivastwa827

Жыл бұрын

Dealing doge ya khud bi solve karoge

Thank you Ankit for sharing :) Here is my approach: with raw_data AS (select transaction_date::date as transaction_date ,date_part('month', transaction_date) as month ,date_part('day', transaction_date) as day ,CASE WHEN type = 'withdrawal' THEN -amount else amount end u_amt from transactions) ,running_sum AS (select * ,SUM(u_amt) OVER(PARTITION BY month ORDER BY transaction_date ) as balance FROM raw_data) select DISTINCT transaction_date, balance from running_sum ORDER BY transaction_date

Superb explanation 👌 👏 👍

@ankitbansal6

Жыл бұрын

Thank you 😊

Nice explanation Ankit.

@ankitbansal6

Жыл бұрын

Thank you 😊

Script: create or replace table transaction44( transaction_id int , type string, amount float, transaction_date datetime ); insert into transaction44 values (19153, 'deposit', 65.90, '2022-07-10 10:00:00'), (53151, 'deposit', 178.55, '2022-07-08 10:00:00'), (29776, 'withdrawal', 25.90, '2022-07-08 10:00:00'), (16461, 'withdrawal', 45.99, '2022-07-08 13:00:00'), (77134, 'deposit',32.60, '2022-07-10 10:00:00');

Awesome 👍

@ankitbansal6

Жыл бұрын

Thank you! Cheers!

Hi Ankit, Thanks for making these amazing videos. Could you please share is there any course for entry level employee on ssms… It will be helpful for many… ❤

Where can we find the sample data and solution

Thanks a lot for video , here is my solution with cte1 as ( SELECT transaction_date ,case when type = 'withdrawal' then -1 * amount else amount end new_amount FROM transactions ) , cte2 as ( select transaction_date , sum(new_amount) new_amnt from cte1 group by transaction_date ) select transaction_date , sum(new_amnt ) over ( partition by date_part('month',transaction_date) order by transaction_date) from cte2 order by 1;

@ankitbansal6

Жыл бұрын

Good. Partition by should have year also

@prashantmhatre9225

Жыл бұрын

@@ankitbansal6 thanks bhai. Below is my modified and it’s working fine now and also accepted on datalemur. with cte1 as ( SELECT date(transaction_date) transaction_date ,case when type = 'withdrawal' then -1 * amount else amount end new_amount FROM transactions ) , cte2 as ( select transaction_date , sum(new_amount) new_amnt from cte1 group by transaction_date ) select transaction_date transaction_day , sum(new_amnt ) over ( partition by date_part('month',transaction_date) order by transaction_date) balance from cte2 order by 1;

Hello Sir with cte as ( select transaction_id,type,amount, date_trunc('day',transaction_date) as transaction_date from transactions ) select distinct transaction_date, SUM(case when type='deposit' then amount else -1 * amount end ) over(partition by EXTRACT(month from transaction_date) order by transaction_date) as balance from cte

Is DATEPART not available on this platform?

Sahi hai but how about doing it without windows function

Hey Ankit, Can you dona sql project, a realtime one? To understand how it works.

@ankitbansal6

Жыл бұрын

Sure

Hi Ankit, your channel is one the best channel for SQL. Big thank you for providing this questions and their explanations. I have one technical (may be little non technical) question to ask: I have started practicing SQL on datalemur, most of the time i got correct answer using the concept of with CTE, however many times when i check website provided solution its rather not that much complex or would i say lengthy compare to what i used to get answer? Do you think it requires area of improvement or should i continue with this approach as i am comfortable with it and also getting correct answer.?? Thanks again

@ankitbansal6

Жыл бұрын

It's a good idea to try solving a problem with multiple approaches. It will give you more confidence. Moreover during interviews you should have more than 1 solution in your mind.

@niharvaishnav7628

Жыл бұрын

@@ankitbansal6 thank you very much 🙂🙂

Thank you so much for these helpful videos .. Kindly upload SQL project videos and please make a video how we can update our resume with SQL related Projects ...?

@ankitbansal6

Жыл бұрын

Sure

@fakhrabashir6471

Жыл бұрын

@@ankitbansal6 Respect from Pakistan

Can u pls provide the code for ssms

Great 🪄🙌

@ankitbansal6

Жыл бұрын

Thank you 😊

SELECT DISTINCT date(transaction_date), sum(case when type='deposit' then amount else -1*amount end) over (partition by extract(month from transaction_date) order by date(transaction_date) ) FROM transactions;

hi ankit sir which recorder are you using?

@ankitbansal6

Жыл бұрын

Chrome addon

my soln: WITH cte AS( SELECT transaction_id, type, transaction_Date, CASE WHEN type = 'withdrawal' then -amount ELSE amount END as amount from transactions), cte2 AS( SELECT transaction_Date, SUM(amount) as balance FROM cte GROUP BY transaction_Date ORDER BY transaction_Date) SELECT transaction_Date, SUM(balance) OVER(PARTITION BY EXTRACT(year FROM transaction_date),EXTRACT(month FROM transaction_Date) ORDER BY transaction_date) as balance FROM cte2;

I am not able to see the problem using the URL provided in the desc section

@BI-Rahul

8 ай бұрын

This question is no longer under free category. It requires a premium subs

Hi Ankit, Thanks for all your videos. I've gained a lot of confidence watching & practicing them(And also landed my dream job🙏). I had a doubt if you could help explain, May be with an example, It would be great. I want to understand what is the difference between below two, if there is any at all? CASE WHEN DIMENSION_FIELD IN ('X', 'Y') THEN SUM(MEASURE_FIELD) END AS AGGREGATION_1, SUM(CASE WHEN DIMENSION_FIELD IN ('X', 'Y') THEN MEASURE_FIELD END) AS AGGREGATION_2 Really appreciate your help, support & guidance. Thanks in advance 😊🙏

@ankitbansal6

Жыл бұрын

Sure will do

@adityabaha

Жыл бұрын

@@ankitbansal6 Thank You 😊

with c2 as ( with c as ( SELECT type,amount,transaction_date::DATE as dt from transactions) SELECT dt, sum(case when type='withdrawal' then -1*amount else amount end) as daily FROM c group by dt ) select dt,sum(daily) over(partition by EXTRACT (MONTH FROM dt) order by dt) from c2

WITH cte AS ( SELECT transaction_date, SUM(CASE WHEN type = 'deposit' THEN amount ELSE -amount END) AS daily_balance FROM transactions GROUP BY transaction_date ) SELECT transaction_date, SUM(daily_balance) OVER (PARTITION BY YEAR(transaction_date), MONTH(transaction_date) ORDER BY transaction_date) AS balance FROM cte ORDER BY transaction_date;

As of today, it is no longer free. It requires subscription :(

Review it WITH Balance_left as ( select DATE( transaction_date ) as transaction_date, SUM( CASE WHEN type= 'deposit' THEN amount WHEN type= 'withdrawal' THEN -amount ELSE 0 END) as balance from transactions group by DATE( transaction_date ) ORDER BY DATE( transaction_date ) ASC ) select transaction_date, SUM( balance ) OVER( PARTITION BY date_part('month', transaction_date ) ORDER BY DATE( transaction_date ) ) as balance FROM Balance_left Love you Sir thanks for your support

@ankitbansal6

Жыл бұрын

Good

SELECT cast(transaction_date as date) as date, sum(CASE WHEN type = 'withdrawal' THEN -1*amount ELSE amount END)OVER(PARTITION BY datepart(MONTH, cast(transaction_date as date)), datepart(year, cast(transaction_date as date)) ORDER BY transaction_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as amountnew from trans2

Dummy Script: create table transact(transaction_id integer, type varchar(20), amount float(10), transaction_date date ) insert into transact(transaction_id,type,amount, transaction_date) values(19153, 'deposit', 65.90, '2022-10-07') insert into transact(transaction_id,type,amount, transaction_date) values(53151, 'deposit', 178.55, '2022-08-07') insert into transact(transaction_id,type,amount, transaction_date) values(29776, 'withdrawal', 25.90, '2022-08-07') insert into transact(transaction_id,type,amount, transaction_date) values(16461, 'withdrawal', 45.99, '2022-08-07') insert into transact(transaction_id,type,amount, transaction_date) values(77134, 'deposit', 32.66,'2022-10-07') insert into transact(transaction_id,type,amount, transaction_date) values(29776, 'deposit', '500', '2022-08-08')

to solve this probleme you need susbcribe to premium , how can i do it without subscribe thanks Ankit