---
# System prepended metadata

title: Interview data engineer

---

# Interview data engineer

###    SESSIONS

<br>
<br>


| id |        when         | doctor_id | patient_id |
|----|---------------------|-----------|------------|
|  1 | 2023-03-01 20:00:00 |        11 |        111 |
|  2 | 2023-04-01 15:00:00 |        11 |        112 |
|  3 | 2023-04-01 15:00:00 |        12 |        221 |
|  4 | 2023-08-01 15:00:00 |        12 |        221 |
|  5 | 2023-09-01 15:00:00 |        12 |        111 |

<br>
<br>

- Come controlli che non ci siano record con id duplicato in una tabella?
 

- Come conti quante sessioni ha sostenuto ogni terapeuta (doctor)?

- Come individui i terapeuti che hanno avuto almeno un paziente che ha cambiato terapeuta?

<br>
<br>

```sql
SELECT * FROM sessions
```

```sql
SELECT id
FROM sessions
GROUP BY 1
HAVING COUNT(*) > 1
```

```sql
WITH patient_doctor AS (
    SELECT
        patient_id,
        doctor_id,
        MAX(when) AS max_when
    FROM sessions
    GROUP BY 1, 2
),

patient AS (
    SELECT patient_id, MAX(when) AS max_when
    FROM sessions
    GROUP BY 1
)

SELECT
    patient_doctor.patient_id,
    patient_doctor.doctor_id
FROM patient_doctor
LEFT JOIN patient
    ON patient_doctor.patient = patient.patient
        AND patient_doctor.max_when != patient.max_when
```