Data Analyst Interview Questions & Answers: Set 19, NGO, Development & Public Sector

SQL

1. 1.Calculate the number of beneficiaries reached per district per programme.

   Hide answer

   Answer: Count distinct beneficiaries, not rows, because one person can receive services many times. Assume services(beneficiary_id, programme_id, district, service_date) and programmes(programme_id, programme_name). Group by district and programme and use COUNT(DISTINCT beneficiary_id). Trap: COUNT(*) counts service visits, which overstates reach in donor reports.

   SELECT s.district,
          p.programme_name,
          COUNT(DISTINCT s.beneficiary_id) AS beneficiaries_reached
   FROM services s
   JOIN programmes p ON p.programme_id = s.programme_id
   WHERE s.service_date >= DATE '2026-01-01'
   GROUP BY s.district, p.programme_name
   ORDER BY s.district, beneficiaries_reached DESC;

2. 2.Find beneficiaries registered more than once under different IDs.

   Hide answer

   Answer: Duplicates under different IDs share real-world identifiers, so group on those instead of the ID. Assume beneficiaries(beneficiary_id, full_name, father_name, dob, nid, village_id). Normalise names with LOWER and TRIM, then find groups with more than one distinct ID. Also check matching national ID (NID) or phone separately. Treat results as possible duplicates for field verification, not automatic deletions, because two people can share a name.

   SELECT LOWER(TRIM(full_name))   AS name_key,
          LOWER(TRIM(father_name)) AS father_key,
          dob, village_id,
          COUNT(DISTINCT beneficiary_id) AS id_count,
          STRING_AGG(beneficiary_id::text, ', ') AS ids
   FROM beneficiaries
   GROUP BY LOWER(TRIM(full_name)), LOWER(TRIM(father_name)),
            dob, village_id
   HAVING COUNT(DISTINCT beneficiary_id) > 1
   ORDER BY id_count DESC;