daily-counter/app/queries/daily_stats.py

import logging
from queries.connection import connection
import streamlit as st

logger = logging.getLogger(__name__)

def get_all_daily_analytics(end_date:str = 'now'):
    user_id = int(st.session_state.user_id)
    try:
        return connection().query('''
             WITH RECURSIVE timeseries(d) AS (
                 VALUES(date(:end_date))
                 UNION ALL
                 SELECT date(d, '-1 day') as d
                 FROM timeseries 
                 WHERE d > date(:end_date, '-30 days')
            ),
            stats AS (
                SELECT 
                    date(timestamp) as d,
                    counter_id,
                    sum(increment) as count
                FROM entries
                WHERE user_id = :user_id
                group by counter_id, date(timestamp)
            )
            select 
                s.d as date,
                case 
                    when counter_id is null then json_object()
                    else json_group_object(name, count) 
                end as counters
            FROM timeseries s
            left outer join stats t on s.d = t.d
            left join counters c on t.counter_id = c.id
            GROUP by s.d
            ''', params={"end_date": end_date, "user_id": user_id })
    except Exception as e:
        logger.error(e)
        return None


def get_daily_analytics(counter_id:int, end_date:str = 'now'):
    user_id = int(st.session_state.user_id)
    try:
        return connection().query('''
            WITH RECURSIVE timeseries(d) AS (
                 VALUES(date(:end_date))
                 UNION ALL
                 SELECT date(d, '-1 day') as d
                 FROM timeseries 
                 WHERE d > date(:end_date, '-7 days')
            ),
            stats AS (
                SELECT 
                    date(timestamp) as d,
                    sum(increment) as count
                FROM entries
                where counter_id = :id
                and user_id = :user_id
                group by date(timestamp)
            )
            SELECT 
                t.d as "date", 
                coalesce(s.count, 0) as count 
            FROM timeseries as t
            LEFT JOIN stats as s on s.d = t.d 
            ''', params={'id': counter_id, "end_date": end_date, "user_id": user_id})
    except Exception as e:
        logger.error(e)
        return None