Crypto Market Algorithm Report in MYSQL

 

select c.algorithm, q1.tot, q2.tot, q3.tot, q4.tot

from coins c

join (select sum(volume) as tot,coins.algorithm from transactions

left join coins on transactions.coin_code = coins.code

where month(dt) between '01' and '03' and year(dt)=2020

group by coins.algorithm) q1 on c.algorithm=q1.algorithm

join (select sum(volume) as tot,coins.algorithm from transactions

left join coins on transactions.coin_code = coins.code

where month(dt) between '04' and '06' and year(dt)=2020

group by coins.algorithm) q2 on c.algorithm=q2.algorithm

join (select sum(volume) as tot,coins.algorithm from transactions

left join coins on transactions.coin_code = coins.code

where month(dt) between '07' and '09' and year(dt)=2020

group by coins.algorithm) q3 on c.algorithm=q3.algorithm

join (select sum(volume) as tot,coins.algorithm from transactions

left join coins on transactions.coin_code = coins.code

where month(dt) between '10' and '12' and year(dt)=2020

group by coins.algorithm) q4 on c.algorithm=q4.algorithm

GROUP BY c.algorithm

ORDER by c.algorithm asc;

Customer Spending in MySQL

 

SELECT c.customer_name, ROUND(SUM(i.total_price), 6)

FROM customer c

INNER JOIN invoice i ON c.id=i.customer_id

GROUP BY c.customer_name

HAVING SUM(i.total_price)<0.25*(SELECT AVG(total_price) FROM invoice)

ORDER BY ROUND(SUM(i.total_price), 6) DESC