WITH nondeleted_quotes AS (
    SELECT
        quotes.id as "Quote ID",
        quotes.instanceid as "Delegation ID",
        companies.name as "Company Name",
        SUM(credits.amount)::numeric / 100.0 as "Quote Amount"
    FROM heroku_public.quotes
    LEFT JOIN heroku_public.credits ON credits.quoteid=quotes.id
    LEFT JOIN heroku_public.companies ON companies.id=credits.companyid
    WHERE quotes.deletedat IS NULL -- AND companies.externalinternal = 'External'
    GROUP BY quotes.id, companies.name
    ORDER BY quotes.instanceid
),

total_quotes AS (
    SELECT 
        "Delegation ID",
        COUNT("Quote ID") as "Total Quotes on Delegation"
    FROM nondeleted_quotes
    GROUP BY "Delegation ID"
),

total_quote_amount AS (
    SELECT
        "Delegation ID",
        SUM("Quote Amount") AS "Total Quote Amount on Delegation"
    FROM nondeleted_quotes
    GROUP BY "Delegation ID"
)

SELECT
    nondeleted_quotes."Quote ID",
    nondeleted_quotes."Delegation ID",
    nondeleted_quotes."Company Name",
    nondeleted_quotes."Quote Amount",
    total_quotes."Total Quotes on Delegation",
    total_quote_amount."Total Quote Amount on Delegation"
FROM nondeleted_quotes
JOIN total_quotes ON total_quotes."Delegation ID"=nondeleted_quotes."Delegation ID"
JOIN total_quote_amount ON total_quote_amount."Delegation ID"=nondeleted_quotes."Delegation ID"
WHERE total_quotes."Total Quotes on Delegation" > 1
ORDER BY "Delegation ID" DESC;