data analyst sql interview questions and answers #43/100 | HSBC | SQL Tutorials | UBER Diwali Bonus

Welcome to Day 43 of my 100 Days Challenge!
Get the question
Type !question 400 -- to get first question & datasets
Type !question 373 -- to get second question & datasets
Join the community (Try SQL Mentor)-- / discord
Join me as I walk through each problem step-by-step, providing detailed explanations and practical tips. Don't forget to like, subscribe, and hit the bell icon to stay updated with daily challenges!
Links:
GitHub Repository - Get the question & datasets
github.com/naji...
1:1 Mentorship - topmate.io/zer...
SQL Live Workshop - zeroanalyst.co...
Excel Live Workshop - zeroanalyst.co...
Best Data Analytics Course:
Data Analyst Courses by Meta - imp.i384100.ne...
Data Analytics Foundation by Google - imp.i384100.ne...
Microsoft Business Analyst Course - imp.i384100.ne...
Microsoft Power Bi End to End Course - imp.i384100.ne...
My Other KZbin Videos -
SQL 30 Days Road Map - [Free Learning Resources]
• SQL For Beginners | Gr...
How to Install PostgreSQL in Windows/Mac - • Learn SQL for Data Ana...
How Import Data into PostgreSQL (PgAdmin 4) - • Effortless Data Import...
Group By Video - • SQL For Beginners | Gr...
#SQL #DataAnalysis #PortfolioProject #DataCleaning #BusinessAnalysis #DataAnalyst #SQLProject #EDA #SQLTutorial #GitHub #DataScience #CareerInData #LearnSQL #SQLforDataAnalyst #AspiringDataAnalyst #ResumeProject #EndToEndProject

[@NareshKumar-xr7gv]

Superb bro.. all the best and thank you

[@dattabandi9226]

Most interesting

[@HarshKoli-r8y]

we can use order by? to get higest rating

[@rishabhralli9151]

with cte as( select * ,count(*) over(partition by driver_id) as cn from rides where end_time is not null and end_time>=timestampadd(month,-3,now())) ,cte1 as( select * from cte where cn>=5), cte2 as( select c1.driver_id,avg(c1.rating) as rat,avg(c1.cn) as tot_ride,dr.driver_name,dr.city from cte1 c1 inner join drivers dr on c1.driver_id=dr.driver_id group by c1.driver_id,dr.driver_name,dr.city) ,cte3 as( select *,rank() over(partition by city order by rat desc) as rn from cte2) select city,driver_name,round(tot_ride,0) as total_rides,rat as avg_rating from cte3 where rn=1 another approach

[@Chathur732]

with cte as ( SELECT city, driver_name, COUNT(CASE WHEN end_time IS NOT NULL THEN 1 END) AS cnt, AVG(rating) AS avg_rating FROM drivers D JOIN rides R ON D.driver_id = R.driver_id WHERE EXTRACT(DAY FROM (CURRENT_DATE - start_time)) = 5 order by city ) select * from ( select *, rank() over(partition by city order by avg_rating desc) as rnk from cte ) where rnk = 1

[@theinsightminer08]

WITH ranked_rides AS (SELECT d.city AS city_name, d.driver_id, d.driver_name, COUNT(r.ride_id) AS total_completed_rides, AVG(r.rating) AS avg_rating, DENSE_RANK() OVER(PARTITION BY d.city ORDER BY AVG(r.rating) DESC) AS rnk FROM Rides r INNER JOIN Drivers d ON d.driver_id = r.driver_id WHERE r.end_time IS NOT NULL AND r.start_time >= current_date() - interval 3 month GROUP BY 1,2,3 HAVING COUNT(r.ride_id) >= 5 ) SELECT city_name, driver_name, total_completed_rides, avg_rating FROM ranked_rides WHERE rnk =1;