EY SQL Interview Question | Medium-level

[@Manifestion_kannada]

I love the each and every explanation in detail you give in every problem.
Make at least 2 videos in a week

[@humdrum2041]

WITH CTE1 AS (SELECT S.SECTOR,T.REVENUE,MONTH(T.TRANSACTION_DATE) DT FROM TRANSACTIONS1 T
INNER JOIN SECTORS S ON S.COMPANY_ID=T.COMPANY_ID
WHERE T.TRANSACTION_DATE>='2020-01-01' AND T.TRANSACTION_DATE

[@anshusharaf2019]

@Nishtha your PPT output is different from your expected output I think it's a mis typing okay.
The second thing why you use cte nd all we can solve with simple inner join and group by months and sector.f
SELECT s.sector, strftime('%m', transaction_date) AS transaction_month, avg(t.revenue) as avg_revanue
FROM Transactions t
INNER join Sectors s on t.company_id = s.company_id
group by s.sector, transaction_month;
Correct Me if I am wrong!

[@datasciencewithnish]

@@anshusharaf2019 Yes, it’s a typo error. Thanks for noticing and updating me.

[@peace_321]

Please tell me that,, i know all basics of MySQL but while solving this complex / advance questions i lag behind. What shall i do please please.. reply...

[@datasciencewithnish]

Practice problems as much as you can. You”ll be able to solve complex problems easily with practice.

[@peace_321]

@@datasciencewithnish thank you soooo much ....

[@raone6923]

Just say its asked in amazon for view nyc trick

[@HARSHRAJ-gp6ve]

Di plese post 2 to 3 query questions each werk
with cte as(
select * FROM Transactions where YEAR(transaction_date)=2020
),cte1 as(
select cte.*,sector FROM cte JOIN Sectors ON cte.company_id=Sectors.company_id
)
select MONTH(transaction_date) as month,sector,AVG(revenue) as avg_revenue FROM cte1 GROUP BY
MONTH(transaction_date),sector;

[@myosith4795]

Can i use group by to solve this problem

[@echodelta7680]

In the first solution, what is the need of extracting year in the WHERE clause?

[@datasciencewithnish]

We've used the extract function in the year using WHERE clause considering if the dataset contains records from multiple years, and you want to filter only the data relevant to a specific year (in this case, 2020). Even though the sample input table specifies that the default year to be 2020, it's crucial to explicitly include the year extraction in the WHERE clause to ensure accuracy, especially when working with real-world data, where records may span across multiple years.
( We're just following instructions according to the question)

[@echodelta7680]

@@datasciencewithnish ah .. got it now. Rather than the solution being very specific, it accommodates for other records too that may have different years. Thanks :)

[@Rathore2305]

Practice file bhi send kro di

[@datasciencewithnish]

Please check the caption for script

[@Half_sql_developer]

select month(transaction_date)as month, d.sector,avg(revenue) as revenue
from transactions t
join department d
on t.company_id = d.company_id
group by month(transaction_date), d.sector
order by month(transaction_date),revenue

[@prajju8114]

easy question, i dont think this is advanced