example.sql

    SELECT
        AVG(tickets.custom_data[rate_responsiveness])*5 AS 'Responsiveness',
        'Responsiveness: {{formatNumber (math value "/" 5) minimumFractionDigits=0 maximumFractionDigits=1}} out of 5' as 'tooltip_text_template'
    FROM tickets
    WHERE
        tickets.custom_data[rate_responsiveness].value <> NULL
        AND tickets.custom_data[external_lawyer] <> NULL
    GROUP BY DPQL_HIERARCHY(tickets.custom_data[external_lawyer], 1) AS 'Firm'
    
    LAYER WITH
    
    SELECT
        AVG(tickets.custom_data[rate_commerciality])*5 AS 'Commerciality',
        'Commerciality: {{formatNumber (math 0_value "/" 5) minimumFractionDigits=0 maximumFractionDigits=1}} out of 5' as 'tooltip_text_template'
    FROM tickets
    WHERE
        tickets.custom_data[rate_commerciality].value <> NULL
        AND tickets.custom_data[external_lawyer] <> NULL
    GROUP BY DPQL_HIERARCHY(tickets.custom_data[external_lawyer], 1) AS 'Firm'
    
    LAYER WITH
    
    SELECT
        AVG(tickets.custom_data[rate_value])*5 AS 'Value',
        'Value: {{formatNumber (math 1_value "/" 5) minimumFractionDigits=0 maximumFractionDigits=1}} out of 5' as 'tooltip_text_template'
    FROM tickets
    WHERE
        tickets.custom_data[rate_value].value <> NULL
        AND tickets.custom_data[external_lawyer] <> NULL
    GROUP BY DPQL_HIERARCHY(tickets.custom_data[external_lawyer], 1) AS 'Firm'
    
    LAYER WITH
    
    SELECT
        AVG(tickets.custom_data[rate_friendliness])*5 AS 'Friendliness',
        'Friendliness: {{formatNumber (math 2_value "/" 5) minimumFractionDigits=0 maximumFractionDigits=1}} out of 5' as 'tooltip_text_template'
    FROM tickets
    WHERE
        tickets.custom_data[rate_friendliness].value <> NULL
        AND tickets.custom_data[external_lawyer] <> NULL
    GROUP BY DPQL_HIERARCHY(tickets.custom_data[external_lawyer], 1) AS 'Firm'