EY SQL Interview Question | Medium-level

[@Iamthebestthing]

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

[@raone6923]

Just say its asked in amazon for view nyc trick

[@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

[@sbharathi3394]

useful thank you!

[@Nishikant_N]

Hi, I think This is correct Approach. Let me clear
WITH Monthly_Revenue AS (
SELECT
s.sector,
FORMAT(t.transaction_date, 'yyyy-MM') AS month_year,
SUM(t.revenue) AS monthly_revenue
FROM
Transaction_Table t
JOIN
Sectors s ON t.company_id = s.company_id
GROUP BY
s.sector, FORMAT(t.transaction_date, 'yyyy-MM')
)
SELECT
sector,
AVG(monthly_revenue) AS avg_monthly_revenue
FROM
Monthly_Revenue
GROUP BY
sector;

[@Savenature635]

My approach :
select month(transaction_date) as month,sector,round(avg(revenue),0) as avg_revenue
from ey_transactions t
left join
ey_sectors s
on t.company_id=s.company_id
where year(transaction_date)=2020
group by 1,2;

[@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 :)

[@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 ....

[@myosith4795]

Can i use group by to solve this problem

[@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;

[@Rathore2305]

Practice file bhi send kro di

[@datasciencewithnish]

Please check the caption for script

[@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.

[@TheHalfDeveloper]

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

[@krantikumarm2705]

SELECT sector, date_format(transaction_date, '%Y-%m') months, ROUND(AVG(revenue),2) avg_rev FROM sectors s
INNER JOIN transactions as t on t.company_id = s.company_id
GROUP BY 1,2;