Was the Second Supply a Preferred Vendor?

Problem

Table: `Contractors`

```text
+------------------+---------+
| Column | Type |
+------------------+---------+
| contractor_id | int |
| onboarded_day | date |
| preferred_vendor | varchar |
+------------------+---------+
contractor_id is the primary key.
```

Table: `Supplies`

```text
+---------------+---------+
| Column | Type |
+---------------+---------+
| supply_id | int |
| supplied_day | date |
| material_id | int |
| contractor_id | int |
+---------------+---------+
supply_id is the primary key. contractor_id is the contractor who supplied
the material. Assume each contractor's supply dates are distinct.
```

Table: `Materials`

```text
+-------------+---------+
| Column | Type |
+-------------+---------+
| material_id | int |
| vendor | varchar |
+-------------+---------+
material_id is the primary key.
```

For each contractor, determine whether the **vendor of the second material
they supplied (ordered by supply date)** equals their `preferred_vendor`. If
the contractor supplied fewer than two materials, report `'no'`. Return
`contractor_id` and a column named `second_supply_preferred` holding `'yes'`
or `'no'`.

**Example**

```text
Contractors:
+---------------+---------------+------------------+
| contractor_id | onboarded_day | preferred_vendor |
+---------------+---------------+------------------+
| 1 | 2023-01-01 | Acme |
| 2 | 2023-01-01 | Globex |
+---------------+---------------+------------------+

Supplies:
+-----------+--------------+-------------+---------------+
| supply_id | supplied_day | material_id | contractor_id |
+-----------+--------------+-------------+---------------+
| 10 | 2023-02-01 | 100 | 1 |
| 11 | 2023-02-05 | 200 | 1 |
| 12 | 2023-03-01 | 300 | 2 |
+-----------+--------------+-------------+---------------+

Materials:
+-------------+--------+
| material_id | vendor |
+-------------+--------+
| 100 | Globex |
| 200 | Acme |
| 300 | Globex |
+-------------+--------+

Output:
+---------------+--------------------------+
| contractor_id | second_supply_preferred |
+---------------+--------------------------+
| 1 | yes |
| 2 | no |
+---------------+--------------------------+
```

Contractor 1's second supply (material 200) is from Acme, which is their
preferred vendor -> yes. Contractor 2 only supplied one material -> no.

Tables

Example rows — the live problem includes the full dataset.

Expected output

Your answer should return 2 rows with the columns contractor_id, second_supply_preferred.

Starter code (SQL)

SELECT *
FROM Contractors;

Related SQL questions

• 3-Consecutive-Week Marketing StreakHard
• 30-Day Rolling Average Trading VolumeHard
• 7-Day Active User Rolling MetricHard
• 7-Day Revenue Moving AverageHard
• Always Mid-Pack BakersHard
• Annual Energy Output Growth by Panel ModelHard