How to add a column in sql as a dimension in order to be used as filter in the dashbaord report

Question

I have a table below:

Date	cust_ecn	msg_type_id	msg_type_desc
2023_01-02	Mark	4	legal Information
2023-01-02	Jack	1	New address
2023-01-02	Jack	2	credit record
2023-01-02	Jack	3	Inquiry
2023-01-02	Linda	2	credit record
2023-01-02	Linda	3	Inquiry

I can use query code to get count of subscription for each cust_ecn:

SELECT date, cust_ecn, count(distinct msg_type_id)
FROM subscription GROUP BY 1, 2

But due to the large volume of data in production db, I can only aggregate data by date:

Select date, count(distinct cust_ecn) cust_cnt, sum(subscription) total_subscription from the relevant table

(Note: I’m not sure if ‘sum(subscription) total_subscription’ is correctly used here in the above query?)

But I also need to use msg_type_desc as dimension filter in a dashboard report. How to implement this in the query code?

I've tried to create sql query below:

SELECT
    date,
    COUNT(DISTINCT cust_ecn) AS cust_cnt,
    MAX(max_subscription_new_address) AS max_subscription_new_address,
    MAX(max_subscription_creditrecord AS max_subscription_creditrecord,
   MAX(max_subscription_creditrecord AS max_subscription_nquiry    
   MAX(max_subscription_LegalInformation AS max_subscription_LegalInformation FROM
 ( select 
date,
        cust_ecn,
        msg_type_desc,
        MAX(CASE WHEN msg_type_desc = 'New Address' THEN COUNT(DISTINCT msg_type_id) ELSE 0 END) AS max_subscription_new_address,
        MAX(CASE WHEN msg_type_desc = 'credit record' THEN COUNT(DISTINCT msg_type_id) ELSE 0 END) AS max_subscription_creditrecord,

      MAX(CASE WHEN msg_type_desc = 'Inquiry' THEN COUNT(DISTINCT msg_type_id) ELSE 0 END) AS max_subscription_Inquiry,

      MAX(CASE WHEN msg_type_desc = 'Legal Information ' THEN COUNT(DISTINCT msg_type_id) ELSE 0 END) AS max_subscription_LegalInformation

    FROM
        The table
    GROUP BY
        date, cust_ecn, msg_type_desc) aa

GROUP BY
    Date

---

I'm not sure if the query above can be used to add the dimension, msg_type_desc as filter correctly?

Thank you for any help.

--RP