Відео

Brilliantly Explained sir .... Mindblowing ... 🙂

I am having trouble in connecting with the MySQL server with the given code using sqlalchemy. is there any another way to connect with the MySQL server?

I think this i great but i do think its more advance than most will ask. I cant see many companies asking these questions to juniors. One because they are pretty complex and two because of time.

select a.team_name,(COALESCE(b.wins,0) +COALESCE(d.lost,0)) as No_Of_matches, COALESCE(b.wins,0) no_of_WINS, COALESCE(d.Lost,0) No_of_Losses from (Select Team_1 as team_name from nishaddb.dbo.icc_world_cup union Select Team_2 from nishaddb.dbo.icc_world_cup )a left join (select winner, count(winner) wins from nishaddb.dbo.icc_world_cup group by winner)b on a.team_name =b.winner left join ( select c.team, sum(c.lost1) Lost from (select Team_1 as team, count(team_1) as lost1 from nishaddb.dbo.icc_world_cup where team_1 <> winner group by team_1 union all select Team_2, count(team_2) as lost1 from nishaddb.dbo.icc_world_cup where team_2 <> winner group by team_2) c group by c.Team )d on a.team_name =d.team

i was getting this error with datetrunc function 'DATETRUNC' is not a recognized built-in function name.'

with cte as ( select *,'Source' as Location from source where id not in (select a.id from source a inner join target b on a.id=b.id and a.name=b.name) union all select *,'target' as Location from target where id not in (select a.id from source a inner join target b on a.id=b.id and a.name=b.name) ) select distinct a.id,case when occurance>1 then 'Mismatch' when lower(Location)='source' then 'New in Source' when lower(Location)='target' then 'New in Source' else null end as comments from cte a inner join (select id,count(id) as occurance from cte group by id )b on a.id=b.id

with cte as ( SELECT *, LEAD(date_sub(bill_date,interval 1 day), 1, '9999-12-31') OVER (PARTITION BY emp_name ORDER BY bill_date) AS bill_date_end FROM billings) select cte.emp_name, sum(bill_rate*bill_hrs) as Total_amount from cte join hoursworked on cte.emp_name = hoursworked.emp_name and hoursworked.work_date between cte.bill_date and bill_date_end group by cte.emp_name

SELECT A.*, DATEDIFF(DAY, A.CREATE_DATE, A.RESOLVED_DATE) AS ACTUAL_DATE, DATEDIFF(DAY, A.CREATE_DATE, A.RESOLVED_DATE) - 2 * DATEDIFF(WEEK, A.CREATE_DATE, A.RESOLVED_DATE) - A.NO_OF_HOLIDAYS AS ACTUAL_WORKING_DAYS FROM ( SELECT TICKET_ID, CREATE_DATE, RESOLVED_DATE, COUNT(HOLIDAY_DATE) AS NO_OF_HOLIDAYS, REASON FROM TICKETS T LEFT JOIN HOLIDAYS H ON H.HOLIDAY_DATE BETWEEN T.CREATE_DATE AND T.RESOLVED_DATE AND DATENAME(WEEKDAY, H.HOLIDAY_DATE) NOT IN ('Saturday', 'Sunday') GROUP BY TICKET_ID, CREATE_DATE, RESOLVED_DATE, REASON ) A;

Someone confirm whether this logic works? First we find count of new customers everyday and then find active customers count similarly. Now by subtracting them we'll get the repeat or continuing customers.

select username, activity, startdate, enddate from ( select *, count(1) over(partition by username) as count_username, rank() over(partition by username order by startdate, enddate) as ranking from useractivity) a where (ranking = 2 ) or (count_username < 2)

1:03 sir you said rolling threesome and that was very funny by the way thank you for video

part 1:Customer Retention with cte as( SELECT *, month(order_date) as month, lag(order_date) over (Partition by cust_id order by order_date) as previous_order_date from transactions ) SELECT month(order_date) as month_1, sum(case when previous_order_date then 1 else 0 end) as count from cte group by month_1 part 2: Customer Churn SELECT *, month(order_date) as month, lead(order_date) over (Partition by cust_id order by order_date) as next_order_date from transactions ) SELECT month(order_date) as month_1, sum(case when next_order_date is null then 1 else 0 end) as count from cte group by month_1 happy SQL coding!!

Thanks Ankit for your guidance. Please have a look below query select sum(case when tc> 1 then 1 else 0 end )as repeat_customer, sum(case when tc= 1 then 1 else 0 end )as new_customer from (select customer_id, count( customer_id) as tc from customer_orders group by customer_id) a ;

If you're facing issues with the creation of engine then you can use this part which is easy to modify: import sqlalchemy as sa import pyodbc import urllib params = urllib.parse.quote_plus( "DRIVER={ODBC Driver 17 for SQL Server};" "SERVER=DEMO\SQL2022;" "DATABASE=Python;" # "UID=domain\username;" # "PWD=*******" "Trusted_Connection=yes" ) engine = sa.create_engine(f"mssql+pyodbc:///?odbc_connect={params}") conn = engine.connect()

Amazing video got to learn so many things <3.

I have query base city like, i need to check piiza send across city like bangalore, mumbai , delhi etc, pls help how write this code using slq table called pizza.

Hi Ankit, Very well explained! Can we use dense_rank here instead of rank()?

I saw multiple videos on this topic, but trust me guy's no one can simplify the lang. or concept other than Mr. Ankit Bansal. Respect you sir for a reason 🙏🙏 thanks a lot❤

with cte_1 as ( select *,date_sub(date_value, interval (rnk) day ) as orignal_date from( select *,row_number() over(partition by state order by date_value ) as rnk from tasks order by 1) new) select min(date_value) as start_date, max(date_value) as end_date, state from cte_1 group by orignal_date,state

MySQL sol: with second as( select seller_id,order_date,item_id,item_brand from ( select seller_id,order_date,item_id,item_brand, dense_rank() over(partition by seller_id order by order_date asc) as rnk from orders join items using(item_id) order by 1) new where rnk =2) select user_id as seller_id, case when item_brand = favorite_brand then 'yes' else 'no' end as 2nd_item_fav_brand from users u left join second s on u.user_id = s.seller_id

Please crct me if i am wrong... My query for findimg users who bought only iphone 15 : With cte as ( select user_id from iphone Group by user_id Having count(*)<2) Select user_id, model from iphone Where user_id in (select user_id from cte) and model="i-15"

Here is one more approach : select e1.emp_id, e1.name, e1.salary,e2.salary, e1.dept_id from emp_salary as e1 join emp_salary e2 on e1.dept_id=e2.dept_id where e1.salary=e2.salary and e1.emp_id != e2.emp_id I think it is easier too. Do let me know. Thanks

if i started learning for now than there are very less data engineering internships(if one has data internship it is easy to get a job ,and that person also get hand on experience ) ,,,,so should i do data analyst first only for internship and after that i will study for data engineering for job because it is also a good way , and i need to do an internship because it is in our college criteria , they do not support much if you do not have done internship?? informative video by the way

My solution for this question- with cte1 as ( select team_1 as team, count(*) as num1 from icc_world_cup group by team_1 union all select team_2 as team, count(*) as num1 from icc_world_cup group by team_2 ), cte2 as ( select team, sum(num1) as Matches_Played from cte1 group by team ), wins as ( select winner as team, count(*) as no_of_wins from icc_world_cup group by winner ) select a.team,a.Matches_Played,coalesce(b.no_of_wins,0) as no_of_wins, a.Matches_Played-coalesce(b.no_of_wins,0) as no_of_loss from cte2 as a left join wins as b on a.team=b.team order by no_of_wins desc;

5th question can we also done used left anti right? like where dep.deptid is null?

with cte as (select a.*,b.first_visit, case when ord_dt = first_visit then 1 else 0 end as flag from cust_orders a left join( select cust_id,min(ord_dt) as first_visit from cust_orders group by cust_id) b on a.cust_id = b.cust_id) select ord_dt,sum(flag) as new_cust_cnt,count(flag)-sum(flag) as reptd_cust_cnt from cte group by ord_dt;

@Ankit Bansal Can you please write the answer for this query.it will take 2 mins A. B 1. 2 2. 2 1. 3 No value 4 Null. Null There are 2 tables .table A have one row where we have blank value(row 4) .please give output for innere join and left join Eagerly waiting for your reply

with first_order as (select customer_id, min(order_date) as first_order_date, sum(order_amount) as sales from customer_orders group by customer_id) select co.order_date, sum(case when co.order_date=fo.first_order_date then 1 else 0 end) as new_customer_cnt, sum(case when co.order_date!=fo.first_order_date then 1 else 0 end) as repeat_customer_cnt, sum(case when co.order_date=fo.first_order_date then sales end) as new_cust_rev, sum(case when co.order_date!=fo.first_order_date then sales end) as repeat_cust_rev from customer_orders co inner join first_order fo on co.customer_id=fo.customer_id group by co.order_date;

Thanks for the videos Her is my approach--> select c.*,count(holiday_date) as hd, case when wek>0 then days-wek*2-count(holiday_date) else days end as actua_days from (select ticket_id,create_date,resolved_date, DATEDIFF(day,create_date,resolved_date) as days, DATEDIFF(WEEK,create_date,resolved_date) as wek from tickets) c left join holidays h on holiday_date between create_date and resolved_date group by ticket_id,days,wek,create_date,resolved_date,holiday_date having datename(dw,holiday_date) not in('Sunday','Saturday') or count(holiday_date)=0;

select team1 as Team_Name,k as Matches_Played,coalesce(w,0) as No_of_Wins,k-coalesce(w,0) as No_of_Loses from( select team1,count(team1)as k from( select team1,team2 from cric_table union all select team2,team1 from cric_table )a group by team1 )q left join( select winner,count(winner) w from cric_table group by winner)r on q.team1 = r.winner

create table purchases( user_id int, product_id int, quantity int, purchase_date datetime ); insert into purchases values(536, 3223, 6, '2022-01-11 12:33:44'); insert into purchases values(827, 3585, 35, '2022-01-20 14:05:26'); insert into purchases values(536, 3223, 5, '2022-03-02 09:33:28'); insert into purchases values(536, 1435, 10, '2022-03-02 08:40:00'); insert into purchases values(827, 2452, 45, '2022-04-09 00:00:00'); select * from purchases;

❤❤❤

declare @x int set @x=4; declare @dat char(10); set @dat= '2024-07-06'; select DateAdd(DAY,(7-day(@dat))+(@x-1)*7 ,@dat); what do u think?

Love you sir, love you

Hi Ankit, I can't see the Japanese characters in title post changing the dtype to nvarchar it's showing question marks. I've been searching what could be the reason. Need you suggestion to resolve this.

Thank you so much Ankit i was really struggling to understanding the concept of CTE you have cleared it with First_visit as ( select customer_id,min(order_date) as first_visit_date from customer_orders group by customer_id) , visit_flag as ( select co.*,fv.first_visit_date, case when co.order_date = fv.first_visit_date then 1 else 0 end as First_visit_flag, case when co.order_date != fv.first_visit_date then 1 else 0 end as Repeated_visit_flag, case when co.order_date = fv.first_visit_date then order_amount else 0 end as New_customer_order_amount, case when co.order_date != fv.first_visit_date then order_amount else 0 end as Repeated_customer_order_amount from customer_orders co inner join First_visit FV on co.customer_id = FV.customer_id --- if we do we will get the each customer firstdate we will get ) select order_date, sum(First_visit_flag) as total_new_customer, sum(Repeated_visit_flag) as total_repeated_customer, Sum(New_customer_order_amount) as New_customer_order_amount, Sum(Repeated_customer_order_amount) as Repeated_customer_order_amount from visit_flag group by order_date ;

Simple and good explanation😊

Thanks for sharing Ankit!. Here is my attempt: CREATE TEMPORARY TABLE cricket.Total_Played AS SELECT team, IFNULL(SUM(occur), 0) AS Total_Matches_Played FROM ( SELECT Team_A AS team, COUNT(*) AS occur FROM cricket.matches GROUP BY Team_A UNION ALL SELECT Team_B AS team, COUNT(*) AS occur FROM cricket.matches GROUP BY Team_B ) AS a GROUP BY team; create temporary table cricket.Total_Matches_Won as select Match_Won, count(*) as total_won from cricket.matches group by 1 select team,Total_Matches_Played,ifnull((Total_Matches_Played-total_won),Total_Matches_Played) as Lost_Matches, ifnull(total_won,0) as Won_Matches from naveen.Total_Played a left join cricket.Total_Matches_Won b on a.team=b.Match_Won