How to find the Quarterly Transaction Summary by Customer in MySQL/Snowflake.

In Snowflake we had considered a table called customer_transactions.

For getting month,year and quarter based on year we used direct methods.so we got the result extracted from transaction_date. and added the amount_spent column as well as well.

Once we got the month, quarter and year we can use these values directly to find the amount spent in a quarter base on case statement.

below is way we got the amount spent on quarter 4 as the name is jk we can see in above image there are only 2 records.

we get the total amount spend using sum() and write the case statemtnt inside it.

i had removed unnecessary columns since name is must and q4_amount is calculated based on year so i included it and in group by clause i added year.

we can add the remaining columns as well for finding other quarters as well.

based on quarterly we found the final_amount.

I had found for all quarters q1,q2,q3,q4 below is the final data

Below is the snippet for debugging.

SELECT
    QUERY_ID,
    USER_NAME,
    START_TIME,
    ERROR_CODE,
    ERROR_MESSAGE,
    QUERY_TEXT
FROM
    TABLE(INFORMATION_SCHEMA.QUERY_HISTORY())
WHERE
    QUERY_ID = 'quqry_id';