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;
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
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)
@@datasciencewithnish ah .. got it now. Rather than the solution being very specific, it accommodates for other records too that may have different years. Thanks :)
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;
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...
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
@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!
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;
I love the each and every explanation in detail you give in every problem.
Make at least 2 videos in a week
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;
Just say its asked in amazon for view nyc trick
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
useful thank you!
In the first solution, what is the need of extracting year in the WHERE clause?
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)
@@datasciencewithnish ah .. got it now. Rather than the solution being very specific, it accommodates for other records too that may have different years. Thanks :)
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;
Can i use group by to solve this problem
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...
Practice problems as much as you can. You”ll be able to solve complex problems easily with practice.
@@datasciencewithnish thank you soooo much ....
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
@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!
@@anshusharaf2019 Yes, it’s a typo error. Thanks for noticing and updating me.
Practice file bhi send kro di
Please check the caption for script
easy question, i dont think this is advanced
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;