Verified References on Each Order

Problem

Table: `Member`

```text
+-------------+---------+
| Column | Type |
+-------------+---------+
| member_id | int |
| member_name | varchar |
| email | varchar |
+-------------+---------+
member_id is the primary key. A platform member with a registered email.
```

Table: `Reference`

```text
+-------------+---------+
| Column | Type |
+-------------+---------+
| member_id | int |
| ref_name | varchar |
| ref_email | varchar |
+-------------+---------+
Each row is a reference contact a member listed. A reference is **verified**
when its ref_email matches the email of some registered member.
```

Table: `Order`

```text
+-----------+---------+
| Column | Type |
+-----------+---------+
| order_id | int |
| total | int |
| member_id | int |
+-----------+---------+
order_id is the primary key. An order placed by a member.
```

For each order report: `order_id`, the ordering member's `member_name`, the order `total`, the number of references that member listed (`refs_cnt`), and how many of those references are verified (`verified_cnt`). Order the result by `order_id`.

**Example**

```text
If a member listed 3 references and 2 of their ref_emails belong to
registered members, that order shows refs_cnt = 3 and verified_cnt = 2.
```

Tables

Example rows — the live problem includes the full dataset.

Expected output

Your answer should return 2 rows with the columns order_id, member_name, total, refs_cnt, verified_cnt.

Starter code (SQL)

SELECT *
FROM Member;

Related SQL questions

• 3-Day Tweet Rolling AverageMedium
• 7-Day vs 28-Day Rolling Avg DivergenceMedium
• A/B Test Sample Ratio Mismatch (SRM) CheckMedium
• Accounts Streaming From Two Devices at OnceMedium
• Ad Campaign Funnel AnalysisMedium
• Agent Resolution RateMedium