Deloitte | COGNIZANT | IBM | CAPGEMINI
WITH connection_usage AS (
SELECT
connection_ref,
COALESCE(SUM(internet_mb), 0) / 1024.0 AS connection_internet_gb
FROM daily_service_consumption
WHERE consumption_date >= '2024-02-01' AND consumption_date < '2024-03-01'
GROUP BY connection_ref
),
bill_details AS (
SELECT
b.bill_ref,
b.connection_ref,
b.payable_amount,
LEAST(b.payable_amount, COALESCE(SUM(CASE WHEN t.transaction_state = 'SUCCESS' THEN t.transaction_amount ELSE 0 END), 0)) AS bill_collected_amount
FROM monthly_service_bills b
LEFT JOIN bill_transactions t ON b.bill_ref = t.bill_ref
WHERE b.cycle_month = '2024-02-01' AND b.bill_state != 'CANCELLED'
GROUP BY b.bill_ref, b.connection_ref, b.payable_amount
),
connection_bills AS (
SELECT
connection_ref,
SUM(payable_amount) AS total_conn_bill_amount,
SUM(bill_collected_amount) AS total_conn_collected_amount
FROM bill_details
GROUP BY connection_ref
)
SELECT
p.package_ref,
p.package_label,
COUNT(DISTINCT mc.connection_ref) AS active_connection_count,
ROUND(SUM(COALESCE(cu.connection_internet_gb, 0)), 2) AS total_internet_gb,
ROUND(SUM(cb.total_conn_bill_amount), 2) AS total_bill_amount,
ROUND(SUM(cb.total_conn_collected_amount), 2) AS total_collected_amount,
ROUND(SUM(cb.total_conn_bill_amount) - SUM(cb.total_conn_collected_amount), 2) AS pending_amount,
ROUND(
CASE
WHEN SUM(cb.total_conn_bill_amount) = 0 THEN 0.00
ELSE (SUM(cb.total_conn_collected_amount) / SUM(cb.total_conn_bill_amount)) * 100
END,
2
) AS collection_precentage
FROM service_packages p
INNER JOIN mobile_connections mc ON p.package_ref = mc.package_ref
INNER JOIN connection_bills cb ON mc.connection_ref = cb.connection_ref
LEFT JOIN connection_usage cu ON mc.connection_ref = cu.connection_ref
WHERE mc.connection_state = 'ACTIVE'
AND p.package_enabled = 1
GROUP BY p.package_ref, p.package_label
ORDER BY collection_precentage DESC, p.package_label ASC;
FLIPKART✅
SQL✅
450 ·