data querying

import sqlite3
import random
import datetime as dt
import pandas as pd

conn = sqlite3.connect(":memory")
c = conn.cursor()

years = [2023, 2024, 2025]
main_topics = ["ML", "VA"]
subtopics = ["AB", "CD", "EF"]

data = []
# Car crash code.
for year in years:
    for topic in main_topics:
        for subtopic in subtopics:
            for x in range(1000):
                month = random.randint(1, 12)
                day = random.randint(1, 28)
                duration = random.randint(10, 1000)  # seconds
                start = dt.datetime(year, month, day)
                data.append(
                    [
                        f"{year}_{topic}_{subtopic}_{x}",
                        start,
                        start + dt.timedelta(seconds=duration),
                    ]
                )

c.execute("""
    CREATE TABLE IF NOT EXISTS test (
        task VARCHAR,
        start DATETIME,
        end DATETIME
    )
    """
)
conn.commit()

c.executemany(
    """
    INSERT INTO test (
        task,
        start,
        end
    )
    VALUES (
        ?,
        ?,
        ?
    )
    """
, data)
conn.commit()

c.execute("""
    WITH _decluttered AS (
        SELECT
            SUBSTRING(task, 0, 5) AS task_year,
            start,
            end,
            TIMEDIFF(start, end) AS duration
        FROM
            test
    )
    SELECT
        task_year,
        SUM(duration)
    FROM
        _decluttered
    GROUP BY
        task_year
    """
)

print(c.fetchall())
c.close()
conn.close()