Find Longest streak for habits

[johnconar]

HI
I am creating a habit tracking application and have to find the longest Streak for a given habit. for ex:- if a user swim for 5 straight days than he create a streak of 5 days. But i want if the user miss to chekoff the habit on any day. That mean he breaks his streak .
than the streak in table 1 get back to 0. But in table 2 it should be at 5 days.

Here are my two tables

def create_tables(db):
    cur = db.cursor()

    cur.execute("""CREATE TABLE IF NOT EXISTS Habit (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    task_specification TEXT,
    period TEXT,
    created_at BOOLEAN,
    streak INT ) """)

    cur.execute("""CREATE TABLE IF NOT EXISTS count (
    name TEXT,
    created_at BOOLEAN,
    FOREIGN KEY (name) REFERENCES Habit(name))""")

    db.commit()

functions i have created in the database

# find longest streak for specified habit

def longest_streak(db, name):
    cur = db.cursor()
    cur.execute('SELECT streak FROM count WHERE name=?', (name,))
    long = cur.fetchall()
    for row in long:
        print("Here is your longest streak ")
        print(row[0])


# find longest streak for all habits in the database

def longest_of_all(db):
    cur = db.cursor()

    cur.execute('SELECT name, MAX(streak) FROM count')
    long_all = cur.fetchall()
    for row in long_all:
        print("Here is your longest streak of all habits")
        print(row[0] + row[1])

Actually my functions are not working as i want . Please help me to make it work

Gribouillis write Sep-10-2022, 05:41 PM:
Please post all code, output and errors (it it's entirety) between their respective tags. Refer to BBCode help topic on how to post. Use the "Preview Post" button to make sure the code is presented as you expect before hitting the "Post Reply/Thread" button.