SELECT 
    i.invoice_number,
    c.customer_name,
    COUNT(co.id) AS number_of_contacts
FROM 
    customer c
JOIN 
    invoice i ON c.id = i.customer_id
LEFT JOIN 
    contact co ON c.id = co.customer_id AND co.contact_start_time < i.invoice_date
LEFT JOIN 
    user_account ua ON co.user_account_id = ua.id
WHERE 
    i.user_account_id NOT IN (
        SELECT DISTINCT user_account_id 
        FROM contact 
        WHERE customer_id = c.id AND contact_start_time < i.invoice_date
    )
GROUP BY 
    i.id, c.id
ORDER BY 
    i.invoice_number ASC

#include <iostream>
#include <vector>
#include <queue>
using namespace std;
int minimumPower(vector<int>& cells) {
    priority_queue<int, vector<int>, greater<int>> minHeap(cells.begin(), cells.end());
    
    int totalPower = 0;
        while (minHeap.size() > 1) {
        int first = minHeap.top();
        minHeap.pop();
        int second = minHeap.top();
        minHeap.pop();
        
        int power = first + second;
        totalPower += power;
        
        minHeap.push(power);
    }
    
    return totalPower;
}

SELECT 
    CASE 
        WHEN t.table_name = 'offline_sites' THEN 'Offline'
        WHEN t.table_name = 'online_sites' THEN 'Online'
        WHEN t.table_name = 'suspended_sites' THEN 'Suspended'
    END AS status,
    COUNT(*) AS total_sites
FROM (
    SELECT 'offline_sites' AS table_name, customer_id, domain_name, is_active FROM offline_sites
    UNION ALL
    SELECT 'online_sites' AS table_name, customer_id, domain_name, is_active FROM online_sites
    UNION ALL
    SELECT 'suspended_sites' AS table_name, customer_id, domain_name, is_active FROM suspended_sites
) t
JOIN customers c ON t.customer_id = c.id
WHERE c.is_active = 1 AND t.is_active = 1
GROUP BY t.table_name
ORDER BY 
    CASE 
        WHEN t.table_name = 'offline_sites' THEN 1
        WHEN t.table_name = 'online_sites' THEN 2
        WHEN t.table_name = 'suspended_sites' THEN 3
    END;

SELECT 
    c.name,
    ROUND(SUM(CASE WHEN EXTRACT(QUARTER FROM t.dt) = 1 THEN t.amount ELSE 0 END), 2) AS q1_amount,
    ROUND(SUM(CASE WHEN EXTRACT(QUARTER FROM t.dt) = 2 THEN t.amount ELSE 0 END), 2) AS q2_amount,
    ROUND(SUM(CASE WHEN EXTRACT(QUARTER FROM t.dt) = 3 THEN t.amount ELSE 0 END), 2) AS q3_amount,
    ROUND(SUM(CASE WHEN EXTRACT(QUARTER FROM t.dt) = 4 THEN t.amount ELSE 0 END), 2) AS q4_amount,
    COUNT(t.coin_id) AS total_transactions,
    ROUND(SUM(t.amount), 2) AS total_amount
FROM 
    coins c
LEFT JOIN 
    transactions t ON c.id = t.coin_id AND EXTRACT(YEAR FROM t.dt) = 2023
GROUP BY 
    c.name
ORDER BY 
    c.name ASC;