Second Most Recent Service per Vehicle

Problem

Table: `ServiceVisit`

```text
+------------+---------+
| Column | Type |
+------------+---------+
| plate | varchar |
| job | varchar |
| opened_on | date |
| closed_on | date |
+------------+---------+
There is no single primary key; (plate, opened_on) is unique. Each row is a
service job carried out on a vehicle, with the day it opened and closed.
```

For each vehicle (`plate`), return its **second most recent** service visit, judged by `opened_on` (a later `opened_on` is more recent). If a vehicle has **only one** visit, return that single visit instead.

Return `plate`, `job`, `opened_on`, and `closed_on`. Rows may be in any order. Assume each vehicle's `opened_on` dates are distinct.

**Example**

```text
ServiceVisit:
+---------+-----------+------------+------------+
| plate | job | opened_on | closed_on |
+---------+-----------+------------+------------+
| AB-12 | oil | 2023-01-10 | 2023-01-10 |
| AB-12 | brakes | 2023-03-01 | 2023-03-02 |
| AB-12 | tires | 2023-05-04 | 2023-05-04 |
| ZX-99 | wash | 2023-02-01 | 2023-02-01 |
+---------+-----------+------------+------------+

Output (the second-newest for AB-12 is 'brakes'; ZX-99 has one visit):
+---------+-----------+------------+------------+
| plate | job | opened_on | closed_on |
+---------+-----------+------------+------------+
| AB-12 | brakes | 2023-03-01 | 2023-03-02 |
| ZX-99 | wash | 2023-02-01 | 2023-02-01 |
+---------+-----------+------------+------------+
```

Tables

Example rows — the live problem includes the full dataset.

Expected output

Your answer should return 2 rows with the columns plate, job, opened_on, closed_on.

Starter code (SQL)

SELECT *
FROM ServiceVisit;

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