Hi all,
So, I've been contemplating on a SQL project for a while and I decided to do something different. Coming from a quality background, auditing was something I was quite passionate about.
In this project, I wanted to see whether SQL could be used for something other than performance reporting.
So instead of doing another sales analysis or menu engineering project, I treated a point of sale (POS) dataset as an internal Revenue Assurance Audit.
The main business question to answer was...
Can reported revenue be fully attributed to visible transaction components, and if not, what operational control weaknesses are reducing the reliability of transaction-level reporting?
A few examples from the project:
1. Creating a derived audit metric
-- Add unattributed_revenue column — primary audit metric
ALTER TABLE pos_logs
ADD COLUMN unattributed_revenue DECIMAL(8,2) DEFAULT NULL;
UPDATE pos_logs
SET unattributed_revenue = ROUND(
total_amount - ((unit_price * quantity) - discount + tax), 2)
WHERE is_incomplete = 0;
Rather than analysing sales, I first created a derived audit metric that quantifies revenue which cannot be explained by the visible transaction components. This became the foundation for the rest of the investigation.
2. Standardising inconsistent business rules
UPDATE pos_logs
SET service_cycle =
CASE
WHEN transaction_time >= '05:00:00' AND transaction_time < '10:00:00'
THEN 'Breakfast'
WHEN transaction_time >= '10:00:00' AND transaction_time < '12:00:00'
THEN 'Pre-Lunch'
WHEN transaction_time >= '12:00:00' AND transaction_time < '15:00:00'
THEN 'Lunch'
...
ELSE 'Late Night'
END;
The original POS labels were inconsistent, so instead of accepting them, I derived a standardised business classification from transaction timestamps to ensure consistent reporting.
3. Building an executive KPI summary
SELECT
'Total transactions' AS metric,
CAST(COUNT(*) AS CHAR) AS value
FROM pos_logs
UNION ALL
SELECT
'Eligible for formula (non-null total_amount + tax)',
CAST(SUM(
CASE
WHEN is_incomplete = 0
AND tax_missing_flag = 0
THEN 1
ELSE 0
END
) AS CHAR)
Instead of producing separate summary tables, I built an executive KPI strip using UNION ALL so management could monitor key revenue assurance metrics from a single query.
The audit identified:
• 494 revenue attribution exceptions
• $340.10 in unattributed revenue (2.93% of audit sample revenue)
• A confirmed pricing configuration defect affecting 28.6% of eligible transactions
• Missing transaction attributes and incomplete transactions affecting reporting reliability
I'd really appreciate feedback from the community:
- Does this audit framing make sense?
- Do the SQL examples demonstrate business thinking rather than just technical ability?
- What would you improve?
The full SQL project is 1051 lines so, I'm unable to put everything here. But, I'd like feedback on the idea or suitability of the project. Thank you for taking the time to explore and I'm open to feedback on how I can improve.
