MySQL Common Table Expression (CTE).sql

/*
file: MySQL Common Table Expression (CTE).sql
language: SQL
author: mail@agungpambudi.com Agung Pambudi
description: SQL Advanced Crypto

Enter your query below.
Please append a semicolon ";" at the end of the query
*/

WITH qrt_volume AS (
    SELECT
        algorithm,
        SUM(volume) AS volume,
        QUARTER(dt) AS quarters
    FROM coins c
    JOIN transactions t ON t.coin_code = c.code
    WHERE YEAR(dt) = 2020
    GROUP BY algorithm, QUARTER(dt)
)

SELECT
    c.algorithm,
    q1.volume AS transactions_Q1,
    q2.volume AS transactions_Q2,
    q3.volume AS transactions_Q3,
    q4.volume AS transactions_Q4
FROM coins c
LEFT JOIN qrt_volume q1 ON c.algorithm = q1.algorithm AND q1.quarters = 1
LEFT JOIN qrt_volume q2 ON c.algorithm = q2.algorithm AND q2.quarters = 2
LEFT JOIN qrt_volume q3 ON c.algorithm = q3.algorithm AND q3.quarters = 3
LEFT JOIN qrt_volume q4 ON c.algorithm = q4.algorithm AND q4.quarters = 4
WHERE c.code NOT LIKE 'DOGE' ORDER BY algorithm;