Compatibility with MySQL
Hi,
I am trying to reproduce your queries using MySQL. I noticed that you are using PostGresSQL. Some functions such as DATE_TRUNC seems not compatible with MySQL . I use instead the fonction MONTH() to extract the month. Beside, I get an error in MySQL saying that I can not use LAG in this context?
Window functions seems very powerful and I am just starting to understand it. Could you point me to a tutorial or video about Window functions in SQL?
Here is my MySQL code:
SELECT MONTH(created_at) AS month,
ROUND(
SUM(value - LAG(SUM(value), 1) OVER (ORDER BY MONTH(created_at))) /
LAG(SUM(value),1) OVER (ORDER BY MONTH(created_at)) *100, 2
) perc_diff
FROM transactions
GROUP BY 1
ORDER BY 1 ASC;
I wrote the following query in MySQL,
I would be grateful if instructors checked it.
select * from transactions;
with cte_sum as
(select
month(created_at) as period,
sum(value) as sum
from transactions
group by month(created_at))
select a.period,
a.sum,
lag(a.sum) over w,
ROUND((a.sum/lag(a.sum) over w-1)*100,2)
from cte_sum a
window w as ();