Crop Insurance Audit Sample

A crop insurer is auditing its book. A policy goes into the audit sample when both of these hold:

• its plan_code is shared with at least one other policy, and
• its field location, the pair (zone, plot_no), is held by no other policy.

Return one row with one column, qualified_value: the total insured_value of the sampled policies, rounded to 2 decimals. If no policy qualifies, return a single row containing NULL.

Tables

crop_policies — One row per crop insurance policy.

Example

crop_policies

Expected output

Rows may be returned in any order. Your query also runs against hidden datasets with tricky edge cases.

SQL Path · Subqueries & CTEs · problem 29 of 70 · SQLite

The idea

Each condition is a question about the whole table, not about the row on its own, so answer each one with a grouped subquery.

Plans that appear more than once: GROUP BY plan_code HAVING COUNT(*) > 1. Locations that appear exactly once: GROUP BY zone, plot_no HAVING COUNT(*) = 1. A location is the pair of columns, so group and compare on both; SQLite's row-value syntax (zone, plot_no) IN (…) keeps that tidy. Gluing the two together as text looks equivalent but is not: 'N1' || 12 and 'N11' || 2 are the same string.

Filter the policies with both IN conditions, sum, and round. The alternative computes both counts per row with COUNT(*) OVER (PARTITION BY …), which reads the table once and keeps the conditions side by side. Since SUM of no rows is NULL and an aggregate without GROUP BY always yields one row, the empty case needs no special handling.

Solution

SELECT ROUND(SUM(insured_value), 2) AS qualified_value
FROM crop_policies
WHERE plan_code IN (
        SELECT plan_code
        FROM crop_policies
        GROUP BY plan_code
        HAVING COUNT(*) > 1
      )
  AND (zone, plot_no) IN (
        SELECT zone, plot_no
        FROM crop_policies
        GROUP BY zone, plot_no
        HAVING COUNT(*) = 1
      );

Another way

WITH counted AS (
  SELECT insured_value,
         COUNT(*) OVER (PARTITION BY plan_code)     AS plan_count,
         COUNT(*) OVER (PARTITION BY zone, plot_no) AS plot_count
  FROM crop_policies
)
SELECT ROUND(SUM(insured_value), 2) AS qualified_value
FROM counted
WHERE plan_count > 1
  AND plot_count = 1;

Why common answers fail

Checks only plot_no for uniqueness, but plot numbers repeat across zones, so the location is the pair (zone, plot_no).

SELECT ROUND(SUM(insured_value), 2) AS qualified_value
FROM crop_policies
WHERE plan_code IN (SELECT plan_code FROM crop_policies GROUP BY plan_code HAVING COUNT(*) > 1)
  AND plot_no IN (SELECT plot_no FROM crop_policies GROUP BY plot_no HAVING COUNT(*) = 1);

Glues zone and plot_no into one string, so different locations such as ('N1', 12) and ('N11', 2) both become 'N112' and look shared.

SELECT ROUND(SUM(insured_value), 2) AS qualified_value
FROM crop_policies
WHERE plan_code IN (SELECT plan_code FROM crop_policies GROUP BY plan_code HAVING COUNT(*) > 1)
  AND zone || plot_no IN (SELECT zone || plot_no FROM crop_policies GROUP BY zone || plot_no HAVING COUNT(*) = 1);

The EXISTS for the plan code does not exclude the policy itself, so every policy matches its own plan and unique plans slip through.

SELECT ROUND(SUM(p.insured_value), 2) AS qualified_value
FROM crop_policies AS p
WHERE EXISTS (SELECT 1 FROM crop_policies AS q WHERE q.plan_code = p.plan_code)
  AND NOT EXISTS (SELECT 1 FROM crop_policies AS q
                  WHERE q.zone = p.zone AND q.plot_no = p.plot_no AND q.policy_id <> p.policy_id);

What the interviewer is checking

The interviewer wants to see you translate two "compared with the rest of the table" rules into grouped subqueries or partitioned counts, and treat a composite key as one value.

Mistakes to avoid

• Checking uniqueness on one column of a composite location.
• Concatenating columns into a key without a separator.
• Writing a self-referencing EXISTS without excluding the row itself.
• Rounding each value before summing instead of rounding the total.

Follow-ups to expect

• Return the qualifying policies themselves instead of the total (drop the SUM, keep the filters).
• Count, per zone, how many policies qualify (GROUP BY zone over the filtered rows).
• Make the location rule "no other policy within the same zone and an adjacent plot" (self join on zone and ABS(plot difference) <= 1).

In real work

Audit and fraud teams routinely pick records that share one attribute with peers but are unique on another, such as a common product with an unusual address.

Practice next