customer_rentals

gistfile1.py

import sqlite3

with sqlite3.connect(":memory:") as conn:
    c = conn.cursor()
    
    c.execute(
        """
        CREATE TABLE customers (
            id INTEGER,
            first_name VARCHAR,
            last_name VARCHAR
        )
        """
    )
    c.execute(
        """
        CREATE TABLE rentals (
            customer_id INTEGER,
            date DATETIME
        )
        """
    )
    conn.commit()
    
    customers = [
        (1, "Bob", "Smith"),
        (2, "Jane", "Bloggs")
    ]
    
    c.executemany(
        """
        INSERT INTO customers
        VALUES (?, ?, ?)
        """, customers)
    conn.commit()
    # c.execute("SELECT * FROM customers")
    # print(c.fetchall())
    
    rentals = [
        (1, "2017-01-02"),
        (2, "2017-01-05"),
        (1, "2017-08-02"),
        (2, "2019-01-01")
    ]
    c.executemany(
        """
        INSERT INTO rentals
        VALUES (?, ?)
        """,
        rentals
    )
    conn.commit()
    
    c.execute(
        """
        WITH _rentals AS (
            SELECT 
                CUSTOMER_ID AS customer_id,
                count(CUSTOMER_ID) AS total_rentals
            FROM
                rentals
            WHERE 
                date >= '2017-01-01' 
                AND date <= '2018-01-01'
                AND customer_id = 2
            GROUP BY
                customer_id
        )
        SELECT
            _rentals.customer_id,
            _rentals.total_rentals,
            c.first_name, 
            c.last_name 
        FROM 
            _rentals
        LEFT JOIN
            customers c ON _rentals.customer_id = c.id
    """)
    print(c.fetchall())