Crop Cover for Shared Amounts on Unique Plots — SQL Medium Problem

Problem statement

A farmers' cooperative insures crops plot by plot. For a risk review it needs the 2024 cover of a particular group of policies: those whose 2023 cover amount is shared with at least one other policy, and whose plot is not shared with any other policy. A plot is identified by the pair (village, plot_no) — plot 1 in one village is a different plot from plot 1 in another.

Return one row with one column, total_cover_2024: the sum of cover_2024 over the policies meeting both conditions, rounded to 2 decimal places. If no policy qualifies, the row holds NULL.

Tables

Table: CropPolicy

Primary key: policy_id.

One row per policy. cover_2023 and cover_2024 are the insured amounts in rupees for each year; no column is NULL.

Examples

Example 1

CropPolicy

Output

How to solve Crop Cover for Shared Amounts on Unique Plots

Both conditions compare a policy with the rest of the table, and both can be phrased as membership in a list built by GROUP BY … HAVING:

• the amounts held by more than one policy: SELECT cover_2023 … GROUP BY cover_2023 HAVING COUNT(*) > 1;
• the plots held by exactly one policy: SELECT village, plot_no … GROUP BY village, plot_no HAVING COUNT(*) = 1.

A policy qualifies when its amount is IN the first list and its (village, plot_no) pair is IN the second. Grouping the plot by both columns matters: grouping by plot_no alone would treat plot 1 in Kothur and plot 1 in Wadi as one plot. The policy itself is counted in each group, which is why "shared with another" is > 1 and "not shared" is = 1. Then ROUND(SUM(cover_2024), 2) totals the survivors; with no survivors, SUM yields NULL, as required.

Window counts do the same in a single scan: COUNT(*) OVER (PARTITION BY cover_2023) and COUNT(*) OVER (PARTITION BY village, plot_no) attach both group sizes to every row, and the outer query filters on them. A correlated version asks directly whether another policy (q.policy_id <> p.policy_id) has the same amount (EXISTS) or the same plot (NOT EXISTS); it is the most literal reading but costs a lookup per row for each condition.

Reference solution (MySQL)

SELECT ROUND(SUM(cover_2024), 2) AS total_cover_2024
FROM CropPolicy
WHERE cover_2023 IN (
        SELECT cover_2023 FROM CropPolicy GROUP BY cover_2023 HAVING COUNT(*) > 1
      )
  AND (village, plot_no) IN (
        SELECT village, plot_no FROM CropPolicy GROUP BY village, plot_no HAVING COUNT(*) = 1
      )

Another way

SELECT ROUND(SUM(cover_2024), 2) AS total_cover_2024
FROM (
  SELECT cover_2024,
         COUNT(*) OVER (PARTITION BY cover_2023) AS same_amount,
         COUNT(*) OVER (PARTITION BY village, plot_no) AS same_plot
  FROM CropPolicy
) t
WHERE same_amount > 1 AND same_plot = 1

Another way

SELECT ROUND(SUM(p.cover_2024), 2) AS total_cover_2024
FROM CropPolicy p
WHERE EXISTS (SELECT 1 FROM CropPolicy q WHERE q.cover_2023 = p.cover_2023 AND q.policy_id <> p.policy_id)
  AND NOT EXISTS (SELECT 1 FROM CropPolicy q
                  WHERE q.village = p.village AND q.plot_no = p.plot_no AND q.policy_id <> p.policy_id)