Formatting ths big query for human readability ...
SELECT ytd.productcode,ytd.currency,ytd.totalorders,ytd.totalvisits,ytd.totalproductviews,ytd.totalcartadds,ytd.totalunits,ytd.customscore1,ytd.customscore2,ytd.customscore3,14day.totalorders,14day.totalvisits,14day.totalproductviews,14day.totalcartadds,14day.totalunits,14day.customscore1,14day.customscore2,14day.customscore3,7day.totalorders,7day.totalvisits,7day.totalproductviews,7day.totalcartadds,7day.totalunits,7day.customscore1,7day.customscore2,7day.customscore3,1day.totalorders,1day.totalvisits,1day.totalproductviews,1day.totalcartadds,1day.totalunits,1day.customscore1,1day.customscore2,1day.customscore3
FROM (
SELECT
item_t0.p_productcode as productcode, item_t0.p_currency as currency,
SUM( item_t0.p_totalorders ) totalorders,
SUM( item_t0.p_totalvisits ) totalvisits,
SUM( item_t0.p_totalproductviews ) totalproductviews,
SUM( item_t0.p_totalcartadds ) totalcartadds,
SUM( item_t0.p_totalunits ) totalunits, SUM( item_t0.p_customscore1 ) customscore1, SUM( item_t0.p_customscore2 ) customscore2,
SUM( item_t0.p_customscore3 ) customscore3
FROM productanalytics item_t0
WHERE ( item_t0.p_datetime >= '2021-01-01 00:00:00' AND item_t0.p_datetime <= '2021-07-28 23:59:59')
AND (item_t0.TypePkString=8796126871634 )
GROUP BY item_t0.p_productcode , item_t0.p_currency
) ytd
LEFT JOIN (
SELECT
item_t1.p_productcode as productcode, item_t1.p_currency as currency,
SUM( item_t1.p_totalorders ) totalorders,
SUM( item_t1.p_totalvisits ) totalvisits,
SUM( item_t1.p_totalproductviews ) totalproductviews, SUM( item_t1.p_totalcartadds ) totalcartadds, SUM( item_t1.p_totalunits ) totalunits,
SUM( item_t1.p_customscore1 ) customscore1,
SUM( item_t1.p_customscore2 ) customscore2, SUM( item_t1.p_customscore3 ) customscore3
FROM productanalytics item_t1
WHERE ( item_t1.p_datetime >= '2021-07-15 00:00:00' AND item_t1.p_datetime <= '2021-07-28 23:59:59')
AND (item_t1.TypePkString=8796126871634 )
GROUP BY item_t1.p_productcode , item_t1.p_currency
) 14day ON ytd.productcode=14day.productcode AND ytd.currency=14day.currency
LEFT JOIN (
SELECT
item_t2.p_productcode as productcode,
item_t2.p_currency as currency,
SUM( item_t2.p_totalorders ) totalorders,
SUM( item_t2.p_totalvisits ) totalvisits,
SUM( item_t2.p_totalproductviews ) totalproductviews,
SUM( item_t2.p_totalcartadds ) totalcartadds,
SUM( item_t2.p_totalunits ) totalunits,
SUM( item_t2.p_customscore1 ) customscore1,
SUM( item_t2.p_customscore2 ) customscore2, SUM( item_t2.p_customscore3 ) customscore3
FROM productanalytics item_t2
WHERE ( item_t2.p_datetime >= '2021-07-22 00:00:00' AND item_t2.p_datetime <= '2021-07-28 23:59:59')
AND (item_t2.TypePkString=8796126871634 ) GROUP BY item_t2.p_productcode , item_t2.p_currency
) 7day ON ytd.productcode=7day.productcode AND ytd.currency=7day.currency
LEFT JOIN (
SELECT
item_t3.p_productcode as productcode, item_t3.p_currency as currency,
SUM( item_t3.p_totalorders ) totalorders,
SUM( item_t3.p_totalvisits ) totalvisits,
SUM( item_t3.p_totalproductviews ) totalproductviews,
SUM( item_t3.p_totalcartadds ) totalcartadds,
SUM( item_t3.p_totalunits ) totalunits,
SUM( item_t3.p_customscore1 ) customscore1,
SUM( item_t3.p_customscore2 ) customscore2, SUM( item_t3.p_customscore3 ) customscore3
FROM productanalytics item_t3
WHERE ( item_t3.p_datetime >= '2021-07-28 00:00:00' AND item_t3.p_datetime <= '2021-07-28 23:59:59')
AND (item_t3.TypePkString=8796126871634 )
GROUP BY item_t3.p_productcode , item_t3.p_currency
) 1day ON ytd.productcode=1day.productcode AND ytd.currency=1day.currency\G
The table on the left side of a Left Join has to be read raw. There's the bottleneck---this query needs to read 62.9 million rows without benefit of an index, that's a performance killer ...
id: 1
...
possible_keys: NULL
key: NULL
...
rows: 62855913
If you can lose the Left Joins in favour of Inner Joins, you can help this query with covering indexes.
And if you upgrade to 8.0, I think you can restructure this query toward much better efficiency using CTEs.