Leetcode MEDIUM 3586 - WINDOW SELF JOINs in SQL - COVID Recovery Patients | Everyday Data Science скачать в хорошем качестве

Leetcode MEDIUM 3586 - WINDOW SELF JOINs in SQL - COVID Recovery Patients | Everyday Data Science

Question: https://leetcode.com/problems/find-co... SQL Schema: CREATE TABLE patients ( patient_id INT, patient_name VARCHAR(255), age INT ) CREATE TABLE covid_tests ( test_id INT, patient_id INT, test_date DATE, result VARCHAR(50) ) Truncate table patients insert into patients (patient_id, patient_name, age) values ('1', 'Alice Smith', '28') insert into patients (patient_id, patient_name, age) values ('2', 'Bob Johnson', '35') insert into patients (patient_id, patient_name, age) values ('3', 'Carol Davis', '42') insert into patients (patient_id, patient_name, age) values ('4', 'David Wilson', '31') insert into patients (patient_id, patient_name, age) values ('5', 'Emma Brown', '29') Truncate table covid_tests insert into covid_tests (test_id, patient_id, test_date, result) values ('1', '1', '2023-01-15', 'Positive') insert into covid_tests (test_id, patient_id, test_date, result) values ('2', '1', '2023-01-25', 'Negative') insert into covid_tests (test_id, patient_id, test_date, result) values ('3', '2', '2023-02-01', 'Positive') insert into covid_tests (test_id, patient_id, test_date, result) values ('4', '2', '2023-02-05', 'Inconclusive') insert into covid_tests (test_id, patient_id, test_date, result) values ('5', '2', '2023-02-12', 'Negative') insert into covid_tests (test_id, patient_id, test_date, result) values ('6', '3', '2023-01-20', 'Negative') insert into covid_tests (test_id, patient_id, test_date, result) values ('7', '3', '2023-02-10', 'Positive') insert into covid_tests (test_id, patient_id, test_date, result) values ('8', '3', '2023-02-20', 'Negative') insert into covid_tests (test_id, patient_id, test_date, result) values ('9', '4', '2023-01-10', 'Positive') insert into covid_tests (test_id, patient_id, test_date, result) values ('10', '4', '2023-01-18', 'Positive') insert into covid_tests (test_id, patient_id, test_date, result) values ('11', '5', '2023-02-15', 'Negative') insert into covid_tests (test_id, patient_id, test_date, result) values ('12', '5', '2023-02-20', 'Negative') Pandas Schema: data = [[1, 'Alice Smith', 28], [2, 'Bob Johnson', 35], [3, 'Carol Davis', 42], [4, 'David Wilson', 31], [5, 'Emma Brown', 29]] patients = pd.DataFrame({ 'patient_id': pd.Series(dtype='int'), 'patient_name': pd.Series(dtype='str'), 'age': pd.Series(dtype='int') }) data = [[1, 1, '2023-01-15', 'Positive'], [2, 1, '2023-01-25', 'Negative'], [3, 2, '2023-02-01', 'Positive'], [4, 2, '2023-02-05', 'Inconclusive'], [5, 2, '2023-02-12', 'Negative'], [6, 3, '2023-01-20', 'Negative'], [7, 3, '2023-02-10', 'Positive'], [8, 3, '2023-02-20', 'Negative'], [9, 4, '2023-01-10', 'Positive'], [10, 4, '2023-01-18', 'Positive'], [11, 5, '2023-02-15', 'Negative'], [12, 5, '2023-02-20', 'Negative']] covid_tests = pd.DataFrame({ 'test_id': pd.Series(dtype='int'), 'patient_id': pd.Series(dtype='int'), 'test_date': pd.Series(dtype='datetime64[ns]'), 'result': pd.Series(dtype='str') }) #leetcode #datascience #tutorial