SQL for Data Analysis Questions

Join each transaction's date to a fiscal_calendar lookup table on the exact date, and group by the fiscal columns it returns. Model this as a join, not date arithmetic (DATE_TRUNC, manual month offsets), because fiscal period boundaries are a business decision that can be irregular: they don't have to land on calendar month-ends, and no closed-form date formula can capture that reliably. A calendar table stores the mapping as data, once, and every query just joins to it.

The join

SELECT
  fc.fiscal_year,
  fc.fiscal_month_number,
  fc.fiscal_period_label,
  COUNT(t.txn_id) AS txn_count,
  SUM(t.amount)   AS total_amount
FROM transactions t
LEFT JOIN fiscal_calendar fc ON fc.cal_date = t.txn_date
GROUP BY 1, 2, 3
ORDER BY fc.fiscal_year, fc.fiscal_month_number;

Worked example: why the calendar table matters, not just style preference

Run in sqlite3; strftime('%Y-%m', txn_date) is sqlite3's month-truncation function. The equivalent in Postgres is DATE_TRUNC('month', txn_date), in BigQuery DATE_TRUNC(txn_date, MONTH), in SQL Server DATEFROMPARTS(YEAR(txn_date), MONTH(txn_date), 1), all illustrating the same naive date-arithmetic approach the calendar-table join is being compared against.

Seed a small "4-4-5" style fiscal calendar (a retail convention where each quarter's three months split into a 4-week, a 4-week, and a 5-week period) where fiscal period 1 runs 2026-01-18 through 2026-02-14 (crossing the January/February month boundary) and fiscal period 2 starts 2026-02-15 (mid-month):

fiscal_calendar: (2026-01-30, FY2026, P01), (2026-02-01, FY2026, P01), (2026-02-15, FY2026, P02)
transactions:    (1, 2026-01-30, 100), (2, 2026-02-01, 200), (3, 2026-02-15, 50), (4, 2026-03-01, 75)

Calendar-join result:

fiscal_period_label  fiscal_year  fiscal_month_number  txn_count  total_amount
UNMAPPED                                               1          75
FY2026-P01           2026         1                    2          300
FY2026-P02           2026         2                    1          50

Compare that to naive calendar-month grouping (strftime('%Y-%m', txn_date) or equivalently DATE_TRUNC('month', txn_date)) on the exact same rows:

calendar_month  total_amount
2026-01         100
2026-02         250
2026-03         75

The two approaches disagree, and the disagreement is the whole point: fiscal period P01 correctly groups transactions 1 and 2 together (total 300, they're in the same fiscal period even though one is dated in January and one in February), but naive month-grouping splits them apart (100 in "2026-01", part of 250 in "2026-02"). Meanwhile naive "2026-02" of 250 wrongly merges transaction 2 (fiscal P01) with transaction 3 (fiscal P02), two different fiscal periods that happen to share a calendar month. No amount of date-arithmetic cleverness reproduces the calendar table's answer here, because the boundary (Feb 15) isn't a fixed offset from anything, it's whatever the finance calendar says it is that year.

Transaction 4 (2026-03-01) has no row in this intentionally partial calendar, and the LEFT JOIN surfaces it as UNMAPPED rather than silently dropping it, the same silent-drop risk that applies to any join. In production, that's your signal the calendar table needs to be extended, not a query bug.

Trade-offs and pitfalls

• The calendar table has to be maintained and kept current. It needs a row for every date the business might report on, extended annually (or whenever the fiscal calendar is redefined), and if it falls behind, transactions land in UNMAPPED (via the LEFT JOIN) rather than being silently miscategorized, which is the safer failure mode but still needs monitoring.
• Indexing. fiscal_calendar.cal_date as a primary key (as seeded here) makes this an efficient equality join, meaningfully cheaper than a range join; it's an equi-join specifically because the calendar table pre-computes the answer for every date, so you don't need BETWEEN logic at query time.
• Multi-entity / multi-fiscal-year complexity. Companies with subsidiaries on different fiscal calendars (common after M&A) need the calendar table keyed by entity as well as date, an extra join column, not a fundamentally different pattern.
• Auditability is the real payoff. Beyond correctness, centralizing the fiscal logic in one table means a fiscal calendar change (the company shifts its year-end) is a data update in one place, not a hunt through every report's date-arithmetic logic to find and fix every place the old boundary was hardcoded.

How I would investigate it in Excel

1) Validate the export

• Check that the CRM extract has the right date range, required columns, and no obvious duplicates.
• Confirm whether sales means booked revenue, closed-won deals, or another definition.

2) Compare the right time windows

• Look at last week versus the prior week, but also compare against the same days in previous weeks so day-of-week effects do not mislead us.
• Separate the drop into volume, average deal size, and conversion rate if the CRM has pipeline stages.

3) Segment the problem

• Build PivotTables by region, rep, product, channel, and deal stage.
• Check whether the decline is broad-based or isolated to one segment.
• Look for missing large deals, lower activity, or a stage where opportunities stalled.

4) Check for operational causes

• Lost deals, fewer new opportunities, delayed closes, or a sudden change in source mix.
• If the CRM has timestamps, I would also check whether the drop is just a timing issue from late-week logging.

5) Final answer

• I would summarize the biggest driver first, then give one or two supporting pivots or charts.
• If the root cause is unclear, I would be explicit about that and recommend the next data point to collect.

The key is to move from "sales are down" to a specific explanation like "the decline came mainly from one region and a few large deals slipping out of the close window."

Attribute each purchase's revenue by joining it to the buyer's first and last marketing touchpoint, then aggregate by channel under each model separately: a UNION ALL keeps first-touch and last-touch as two labeled result sets rather than blending them into one number. First-touch credits whichever channel started the relationship; last-touch credits whichever channel closed it. Neither model is more correct on its own: a stakeholder who owns awareness and top-of-funnel spend wants first-touch, while a stakeholder optimizing bottom-of-funnel channels (retargeting, paid search bidding) wants last-touch.

Approach

1. Rank a user's touchpoints by time, once ascending (to find the first touch) and once descending (to find the last touch), using ROW_NUMBER() partitioned by user.
2. Take the rn = 1 row from each ranking as that user's first-touch and last-touch channel.
3. LEFT JOIN purchases to each of those two lookups (not INNER JOIN), so a purchase from a user with zero recorded touchpoints still appears, labeled "unknown," instead of silently disappearing from the total.
4. UNION ALL the two attributed sets, tagging each with an attribution_model label, then GROUP BY model and channel.

Handling ties. If two touchpoints share the exact same touch_time for a user (duplicate event logging, same-second clickstream events), ordering by touch_time alone leaves ROW_NUMBER() unstable: which row lands on rn = 1 can differ between runs. Add a deterministic tiebreaker to the ORDER BY: a touchpoint primary key or ingestion sequence, not another text column like channel, since channel names just sort alphabetically and have nothing to do with which touchpoint actually happened first. The worked example below gives touchpoints a touchpoint_id surrogate key for exactly this purpose and includes a genuine cross-channel tie (user 6) to show it resolving deterministically.

When a stakeholder prefers which model. First-touch fits brand and awareness marketing ("which channel introduces us to buyers"); last-touch fits performance marketing and channels billed on last-click, like paid search bidding ("which channel closes the sale"). Neither handles split credit across a multi-touch path; a stakeholder who needs that is really asking for a fractional or time-decay model, which requires accumulating credit across the whole path, not just the min/max touch.

Worked example

Seed data and query (SQLite):

CREATE TABLE touchpoints (
  touchpoint_id INTEGER PRIMARY KEY,
  user_id     INTEGER,
  channel     TEXT,
  touch_time  TEXT
);

CREATE TABLE purchases (
  purchase_id INTEGER PRIMARY KEY,
  user_id     INTEGER,
  purchase_time TEXT,
  revenue     NUMERIC
);

INSERT INTO touchpoints (touchpoint_id, user_id, channel, touch_time) VALUES
  (1, 1, 'google',  '2026-01-01'),
  (2, 1, 'email',   '2026-01-03'),
  (3, 1, 'organic', '2026-01-05'),
  (4, 2, 'social',  '2026-01-02'),
  (5, 3, 'email',   '2026-01-01'),
  (6, 3, 'email',   '2026-01-01'),
  (7, 6, 'zeta',    '2026-01-01'),
  (8, 6, 'alpha',   '2026-01-01');

INSERT INTO purchases VALUES
  (101, 1, '2026-01-06', 100),
  (102, 2, '2026-01-03', 50),
  (103, 3, '2026-01-02', 75),
  (104, 4, '2026-01-02', 30),
  (105, 6, '2026-01-02', 60);

WITH ranked AS (
  SELECT
    user_id, channel, touch_time, touchpoint_id,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY touch_time ASC,  touchpoint_id ASC) AS rn_first,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY touch_time DESC, touchpoint_id ASC) AS rn_last
  FROM touchpoints
),
first_touch AS (SELECT user_id, channel FROM ranked WHERE rn_first = 1),
last_touch  AS (SELECT user_id, channel FROM ranked WHERE rn_last  = 1),
attributed AS (
  SELECT p.user_id, p.revenue, COALESCE(ft.channel, 'unknown') AS channel, 'first_touch' AS attribution_model
  FROM purchases p
  LEFT JOIN first_touch ft ON p.user_id = ft.user_id
  UNION ALL
  SELECT p.user_id, p.revenue, COALESCE(lt.channel, 'unknown') AS channel, 'last_touch' AS attribution_model
  FROM purchases p
  LEFT JOIN last_touch lt ON p.user_id = lt.user_id
)
SELECT attribution_model, channel, SUM(revenue) AS total_revenue
FROM attributed
GROUP BY attribution_model, channel
ORDER BY attribution_model, total_revenue DESC;

Result:

┌───────────────────┬─────────┬───────────────┐
│ attribution_model │ channel │ total_revenue │
├───────────────────┼─────────┼───────────────┤
│ first_touch       │ google  │ 100           │
│ first_touch       │ email   │ 75            │
│ first_touch       │ zeta    │ 60            │
│ first_touch       │ social  │ 50            │
│ first_touch       │ unknown │ 30            │
│ last_touch        │ organic │ 100           │
│ last_touch        │ email   │ 75            │
│ last_touch        │ zeta    │ 60            │
│ last_touch        │ social  │ 50            │
│ last_touch        │ unknown │ 30            │
└───────────────────┴─────────┴───────────────┘

User 1's three touches (google, then email, then organic) split their $100 purchase: google gets credit under first-touch, organic gets credit under last-touch. User 2 and user 3 have only one touch each, so both models agree for them. User 4 purchased with no logged touchpoint at all: both models correctly bucket that $30 as "unknown" rather than dropping it (an INNER JOIN would have silently dropped it) or guessing a channel.

User 6 is the real tiebreak case: two touchpoints logged the same second, in different channels ('zeta', touchpoint_id = 7, and 'alpha', touchpoint_id = 8). Alphabetically 'alpha' sorts before 'zeta', so a channel-based tiebreaker would hand the win to 'alpha'; but touchpoint_id is what actually orders them, and touchpoint 7 ('zeta') was ingested first, so 'zeta' wins the tie under both rankings here, exactly the deterministic-by-ingestion-order behavior the tiebreaker is supposed to produce, and the opposite of what alphabetizing on channel would have given.

Trade-offs & pitfalls

• Complexity: two window-function sorts over touchpoints (O(n log n) each) plus a UNION ALL that doubles the row count of purchases. For a very large purchases table, consider whether both breakdowns are actually needed in one query or would be cheaper as two simpler queries.
• Edge cases: users with zero touchpoints (handled by LEFT JOIN + COALESCE, not INNER JOIN); duplicate/tied touch_time values, resolved deterministically here by touchpoint_id, not by channel (demonstrated with user 6 above), since without a real tiebreaker "first touch" would silently change between runs; a touchpoint logged after the purchase itself, since this simple model doesn't check touch_time against purchase_time (add WHERE touch_time <= purchase_time before ranking if that ordering matters to the business).
• Common wrong turn: writing two separate queries, one per model, and never combining them, so the interviewer has to ask for a single result set. Also common: using INNER JOIN instead of LEFT JOIN, which silently drops purchases from untouched users and understates total attributed revenue without any error or warning.

Direct answer

"MRR as of a date, prorating partial months" has two legitimate but different conventions, and the definition needs pinning before writing SQL. This answer uses month-to-date proration: an active subscription contributes monthly_price x (days elapsed so far in the calendar month containing as_of_date) / (days in that month), capped at as_of_date and floored at the later of the month's start or the subscription's own start date. The alternative convention, full run-rate (count the full monthly price the moment a subscription exists, prorating only for accounting revenue recognition, not the headline MRR figure) is common at many SaaS companies and gives a different number for any subscription that started mid-month; state clearly which one you're using.

Structured elaboration

Approach:

1. Filter to subscriptions active on as_of_date: start_date <= as_of_date AND (end_date IS NULL OR end_date >= as_of_date).
2. For each, compute the overlap window within the calendar month containing as_of_date: overlap_start = GREATEST(start_date, month_start), overlap_cap = LEAST(month_end, as_of_date).
3. Prorate: monthly_price_cents * (overlap_cap - overlap_start + 1) / days_in_month, summed across active subscriptions.

Key points:

• days_in_month must come from the actual calendar month (DATE_TRUNC plus one month minus a day), not a hardcoded 28-31; getting this wrong systematically skews every partial-month subscription in the same direction.
• Keep the whole calculation in integer cents through the arithmetic, converting to dollars only at final display, to avoid compounding floating-point rounding across many subscriptions.

Worked example

Seed data, as_of_date = 2026-03-15 (March has 31 days):

Point-in-time prorated MRR:

WITH params AS (
  SELECT
    DATE '2026-03-15' AS as_of_date,
    DATE_TRUNC('month', DATE '2026-03-15')::DATE AS month_start,
    (DATE_TRUNC('month', DATE '2026-03-15')::DATE + INTERVAL '1 month' - INTERVAL '1 day')::DATE AS month_end
),
active_subs AS (
  SELECT s.subscription_id, s.customer_id, s.start_date, s.monthly_price_cents, p.*
  FROM subscriptions s
  CROSS JOIN params p
  WHERE s.start_date <= p.as_of_date
    AND (s.end_date IS NULL OR s.end_date >= p.as_of_date)
),
calc AS (
  SELECT
    subscription_id, customer_id, monthly_price_cents,
    GREATEST(start_date, month_start) AS overlap_start,
    LEAST(month_end, as_of_date) AS overlap_cap,
    (month_end - month_start + 1) AS days_in_month
  FROM active_subs
)
SELECT
  subscription_id, customer_id, monthly_price_cents, overlap_start, overlap_cap,
  (overlap_cap - overlap_start + 1) AS active_days_in_month,
  days_in_month,
  ROUND(monthly_price_cents * (overlap_cap - overlap_start + 1)::DOUBLE / days_in_month, 2) AS prorated_mrr_cents
FROM calc
ORDER BY subscription_id;

Result (run in DuckDB; T-SQL/Postgres users would use DATEADD/EOMONTH or date_trunc equivalents, the arithmetic is identical):

subscription_id | customer_id | monthly_price_cents | overlap_start | overlap_cap | active_days_in_month | days_in_month | prorated_mrr_cents
----------------+-------------+----------------------+---------------+-------------+-----------------------+----------------+---------------------
              1 |         101 |                 5000 |    2026-03-01 |  2026-03-15 |                    15 |             31 |             2419.35
              2 |         102 |                 3000 |    2026-03-10 |  2026-03-15 |                     6 |             31 |              580.65
              5 |         104 |                 6000 |    2026-03-01 |  2026-03-15 |                    15 |             31 |             2903.23
              6 |         105 |                 1000 |    2026-03-01 |  2026-03-15 |                    15 |             31 |              483.87

Subscriptions 3 and 4 correctly don't appear, they aren't active on the as_of_date (subscription 3 churned March 5; subscription 4 was superseded February 28). Summed and converted to dollars: $63.87 total prorated MRR as of March 15.

Extension: the new / expansion / churned MRR bridge

A point-in-time figure alone doesn't say WHY MRR is moving. The standard SaaS decomposition classifies subscription-level events within the month using each subscription's FULL monthly price (a different, non-prorated convention from the point-in-time figure above; the two must never be added together or expected to reconcile directly).

Detecting continuity (an upgrade, not a churn-then-new pair) uses LAG() per customer to compare each subscription to that same customer's immediately prior one, treating it as a continuation only when the previous subscription's end_date is exactly one day before the new one's start_date:

WITH ordered AS (
  SELECT
    subscription_id, customer_id, start_date, end_date, monthly_price_cents,
    LAG(monthly_price_cents) OVER (PARTITION BY customer_id ORDER BY start_date) AS prev_price_cents,
    LAG(end_date) OVER (PARTITION BY customer_id ORDER BY start_date) AS prev_end_date,
    LEAD(start_date) OVER (PARTITION BY customer_id ORDER BY start_date) AS next_start_date
  FROM subscriptions
),
classified AS (
  SELECT
    subscription_id, customer_id, start_date, end_date, monthly_price_cents, prev_price_cents,
    CASE
      WHEN start_date BETWEEN DATE '2026-03-01' AND DATE '2026-03-31' AND prev_price_cents IS NULL THEN 'new'
      WHEN start_date BETWEEN DATE '2026-03-01' AND DATE '2026-03-31'
           AND prev_end_date = start_date - INTERVAL 1 DAY AND monthly_price_cents > prev_price_cents THEN 'expansion'
      WHEN start_date BETWEEN DATE '2026-03-01' AND DATE '2026-03-31'
           AND prev_end_date = start_date - INTERVAL 1 DAY AND monthly_price_cents < prev_price_cents THEN 'contraction'
      ELSE NULL
    END AS start_event,
    CASE
      WHEN end_date BETWEEN DATE '2026-03-01' AND DATE '2026-03-31'
           AND (next_start_date IS NULL OR next_start_date <> end_date + INTERVAL 1 DAY) THEN 'churned'
      ELSE NULL
    END AS end_event
  FROM ordered
)
SELECT 'new' AS event_type, subscription_id, customer_id, monthly_price_cents AS mrr_delta_cents
FROM classified WHERE start_event = 'new'
UNION ALL
SELECT 'expansion', subscription_id, customer_id, (monthly_price_cents - prev_price_cents)
FROM classified WHERE start_event = 'expansion'
UNION ALL
SELECT 'contraction', subscription_id, customer_id, (monthly_price_cents - prev_price_cents)
FROM classified WHERE start_event = 'contraction'
UNION ALL
SELECT 'churned', subscription_id, customer_id, -monthly_price_cents
FROM classified WHERE end_event = 'churned'
ORDER BY event_type, subscription_id;

Result:

event_type | subscription_id | customer_id | mrr_delta_cents
-----------+------------------+-------------+-----------------
churned    |                3 |         103 |           -2000
expansion  |                5 |         104 |            2000
new        |                2 |         102 |            3000

Customer 104's plan swap is correctly classified as expansion (+$20), not as a churn-then-new pair, because subscription 4's end_date (Feb 28) is exactly one day before subscription 5's start_date (Mar 1), the continuity condition. Customer 103's subscription (subscription 3) is churned (-$20) because nothing replaces it. Customer 102 is genuinely new (+$30), a first subscription with no prior one for that customer.

Net MRR movement for March: SUM(mrr_delta_cents) = 3000 cents, +$30.00 net new MRR for the month.

Complexity

The point-in-time query is a single filter and scalar computation per active subscription, O(n). The bridge adds two window-function passes (LAG/LEAD partitioned by customer), O(n log n) for the per-customer sort, then a constant number of UNION ALL branches over the already-classified rows. Both are cheap at any realistic subscription-table size; the only real cost driver is whether customer_id and start_date are indexed for the partition/sort.

Edge cases

• A subscription active exactly on as_of_date with start_date = as_of_date: overlap_start = overlap_cap = as_of_date, one active day, correctly prorated to a single day's worth of the monthly price, not zero.
• Leap years and variable month lengths: days_in_month is computed from the actual calendar month, not hardcoded, so February in a leap year prorates correctly against 29 days.
• A customer with two subscriptions active at once (e.g., a base plan plus a separately-billed add-on) breaks the "one active subscription per customer" assumption this bridge logic relies on; each subscription line needs to be classified independently rather than assuming a single prior subscription per customer.
• The one-day-gap continuity rule is a specific assumption about this business's billing semantics (old plan ends the day before the new one starts). A business whose old and new subscriptions overlap by a day, or cut over same-day, needs that condition adjusted to match its actual data model; state the assumption explicitly whenever this query ships.

Trade-offs & pitfalls

• The point-in-time prorated MRR ($63.87) and the bridge's net movement (+$30.00) are two different, non-reconcilable numbers built on different conventions (prorated vs full monthly price). Presenting them on the same dashboard without labeling which is which invites a stakeholder to try to reconcile them and conclude the numbers are broken.
• The continuity/gap detection is a judgment call encoded in SQL, not a SQL universal. Every business's billing system handles plan swaps slightly differently; verify the actual gap convention against real data before trusting the classification, rather than assuming the one-day rule used here transfers unchanged.
• This model assumes one active subscription per customer at a time. Multi-subscription accounts need every subscription LINE classified on its own, or a genuine new add-on gets misclassified as an expansion of an unrelated existing plan.
• Keep monetary values in integer cents through every intermediate step; converting to floating-point dollars early and summing many rows is a classic source of cents-level drift that shows up as "why doesn't this tie out to the penny" in a finance review.

Direct answer

Build the check as a set of explicit boolean conditions, one per issue type (negative price, missing currency, unsupported currency), tag each row with which conditions it violates, and then aggregate those tags into a per-issue-type count. Keep the row-level flags and the summary as two separate query outputs: the flagged rows for someone to act on, and the summary counts for a quick health read.

Structured elaboration

1. Define each issue as an explicit boolean, not a single catch-all WHERE. A row can fail more than one check at once, and you want to know that.
2. Surface the flagged rows with the boolean columns attached, so a reviewer can see exactly why a row was flagged without re-deriving it.
3. Summarize by issue type using UNION ALL (or COUNT(*) FILTER (WHERE ...) per condition) so the output answers "how many rows have issue X" even when a row has more than one issue and would otherwise be double-counted in a naive single-condition GROUP BY.
4. This is the same "flag, don't drop" instinct as any data-quality check: invalid rows get surfaced for a decision, not silently filtered out of the analysis.

The same pattern extends past value-range checks to logical-consistency checks, where the problem isn't that one field is invalid in isolation, but that two fields contradict each other (see the alternate scenario below).

Worked example

Seed data: 7 pricing rows, with a supported_currencies reference table containing USD, EUR, GBP.

SELECT
  price_id, product_id, unit_price, currency,
  unit_price < 0 AS is_negative_price,
  currency IS NULL AS is_missing_currency,
  (currency IS NOT NULL AND currency NOT IN (SELECT code FROM supported_currencies)) AS is_unsupported_currency
FROM pricing
WHERE unit_price < 0 OR currency IS NULL OR currency NOT IN (SELECT code FROM supported_currencies)
ORDER BY price_id;

Real output (4 of 7 rows flagged):

Row 5 shows why a single-issue summary would undercount: it's both negative and unsupported currency.

WITH flagged AS (
  SELECT price_id,
    unit_price < 0 AS is_negative_price,
    currency IS NULL AS is_missing_currency,
    (currency IS NOT NULL AND currency NOT IN (SELECT code FROM supported_currencies)) AS is_unsupported_currency
  FROM pricing
)
SELECT 'negative_price' AS issue_type, COUNT(*) FILTER (WHERE is_negative_price) AS row_count FROM flagged
UNION ALL
SELECT 'missing_currency', COUNT(*) FILTER (WHERE is_missing_currency) FROM flagged
UNION ALL
SELECT 'unsupported_currency', COUNT(*) FILTER (WHERE is_unsupported_currency) FROM flagged
ORDER BY row_count DESC;

Real output:

Row 5 correctly contributes to both negative_price and unsupported_currency, so the totals sum to 5, not 4 (the number of distinct flagged rows).

Alternate scenario: a logical-consistency check

The absorbed variant asks about a row where closed_at is set but stage contradicts it, a different flavor of invalid value: not a bad single value, but two fields disagreeing with each other.

SELECT opp_id, stage, closed_at,
  CASE
    WHEN stage IN ('won','lost') AND closed_at IS NULL THEN 'CLOSED_STAGE_MISSING_DATE'
    WHEN stage = 'open' AND closed_at IS NOT NULL THEN 'OPEN_STAGE_HAS_CLOSE_DATE'
    ELSE 'OK'
  END AS consistency_flag
FROM opportunities ORDER BY opp_id;

Real output:

Same "flag every violated condition explicitly" instinct as the pricing check, just applied across two columns instead of within one.

Trade-offs & pitfalls

• A single WHERE unit_price < 0 OR currency IS NULL OR ... with a GROUP BY on a single derived reason string undercounts multi-issue rows; keep each condition as its own boolean so a row can count toward every issue it actually has.
• Deciding what to do with flagged rows (block the load, quarantine to a side table, or just alert) is a separate policy decision from detection; don't conflate "this looks wrong" with "here's what to do about it" in the same query.
• NOT IN (SELECT ...) against a reference table with any NULLs in it returns no rows at all (a classic SQL trap): if supported_currencies.code could ever contain a NULL, use NOT IN (SELECT code FROM supported_currencies WHERE code IS NOT NULL) or rewrite as a NOT EXISTS.