1) Customers with total bill
amount, total paid, and balance
SELECT c.customer_ id, [Link],
SUM([Link]) AS total_ bill,
IFNULL(SUM(p.paid_ amount), 0) AS
total_ paid,
(SUM([Link]) -
IFNULL(SUM(p.paid_ amount), 0)) AS
balance
FROM customers c
LEFT JOIN bills b ON c.customer_ id =
b.customer_ id
LEFT JOIN payments p ON b.bill_ id =
p.bill_ id
GROUP BY c.customer_ id, [Link];
2. Customers who have
overdue bills
SELECT DISTINCT
c.customer_ id, [Link]
FROM customers c
JOIN bills b ON
c.customer_ id = b.customer_ id
WHERE b.due_ date <
CURDATE()
AND [Link] <> 'Paid';
3. Highest bill paid by each
customer
SELECT c.customer_ id, [Link],
MAX([Link]) AS highest_ bill
FROM customers c
JOIN bills b ON c.customer_ id =
b.customer_ id
WHERE [Link] = 'Paid'
GROUP BY c.customer_ id,
[Link];
4. Bills that are paid late
SELECT b.bill_ id, [Link],
b.due_ date, p.payment_ date
FROM bills b
JOIN customers c ON
b.customer_ id = c.customer_ id
JOIN payments p ON b.bill_ id =
p.bill_ id
WHERE p.payment_ date >
b.due_ date;
5. Total revenue collected month-
wise and year- wise
SELECT YEAR(payment_ date)
AS year,
MONTH(payment_ date) AS
month,
SUM(paid_ amount) AS
total_ revenue
FROM payments
GROUP BY
YEAR(payment_ date),
MONTH(payment_ date)
ORDER BY year, month;
6. Customers who paid bills using
more than one payment method
SELECT c.customer_ id, [Link]
FROM customers c
JOIN bills b ON c.customer_ id =
b.customer_ id
JOIN payments p ON b.bill_ id =
p.bill_ id
GROUP BY c.customer_ id, [Link]
HAVING COUNT(DISTINCT
p.payment_ method) > 1;
7. Bills whose amount is greater than
the average bill amount
SELECT *
FROM bills
WHERE amount > (SELECT
AVG(amount) FROM bills);
8. Top 5 customers based on
total payments
SELECT c.customer_ id, [Link],
SUM(p.paid_ amount) AS
total_ paid
FROM customers c
JOIN bills b ON c.customer_ id =
b.customer_ id
JOIN payments p ON b.bill_ id =
p.bill_ id
GROUP BY c.customer_ id,
[Link]
ORDER BY total_ paid DESC
LIMIT 5;
9. Customers who never made
any payment
SELECT c.customer_ id,
[Link]
FROM customers c
WHERE c.customer_ id NOT IN
(
SELECT b.customer_ id
FROM bills b
JOIN payments p ON
b.bill_ id = p.bill_ id
);
10. Bill status using CASE
(dynamic status)
SELECT bill_ id, amount,
due_ date,
CASE
WHEN status = 'Paid' THEN
'Paid'
WHEN due_ date < CURDATE()
THEN 'Overdue'
ELSE 'Pending'
END AS bill_ current_ status
FROM bills;
11. Payment percentage for
each bill
SELECT b.bill_ id, [Link],
SUM(p.paid_ amount) AS
paid,
ROUND((SUM(p.paid_ amount) /
[Link]) * 100, 2) AS
payment_ percentage
FROM bills b
LEFT JOIN payments p ON
b.bill_ id = p.bill_ id
GROUP BY b.bill_ id, [Link];
12. Customers with more than 3
unpaid bills
SELECT c.customer_ id, [Link],
COUNT(b.bill_ id) AS
unpaid_ bills
FROM customers c
JOIN bills b ON c.customer_ id =
b.customer_ id
WHERE [Link] <> 'Paid'
GROUP BY c.customer_ id,
[Link]
HAVING COUNT(b.bill_ id) > 3;
13. Most frequently used payment
method
SELECT payment_ method,
COUNT(*) AS usage_ count
FROM payments
GROUP BY payment_ method
ORDER BY usage_ count DESC
LIMIT 1;
14. Customers who paid all their bills
SELECT c.customer_ id, [Link]
FROM customers c
WHERE NOT EXISTS (
SELECT 1
FROM bills b
WHERE b.customer_ id =
c.customer_ id
AND [Link] <> 'Paid'
);
15. Rank customers by total bill
amount (without window functions)
SELECT c.customer_ id, [Link],
SUM([Link]) AS total_ bill
FROM customers c
JOIN bills b ON c.customer_ id =
b.customer_ id
GROUP BY c.customer_ id, [Link]
ORDER BY total_ bill DESC;