packages/blueprints/gen-ai-chatbot/static-assets/chatbot-genai-components/backend/python/app/repositories/usage_analysis.py [109:179]:
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
    assert 1 <= limit <= 1000, "Limit must be between 1 and 1000."

    assert (from_ and to_) or (
        not from_ and not to_
    ), "Both from_ and to_ must be specified or omitted."

    if from_ is not None and to_ is not None:
        from_str = re.sub(r"(\d{4})(\d{2})(\d{2})(\d{2})", r"\1/\2/\3/\4", from_)  # type: ignore
        to_str = re.sub(r"(\d{4})(\d{2})(\d{2})(\d{2})", r"\1/\2/\3/\4", to_)  # type: ignore
    else:
        today = date.today()
        from_str = today.strftime("%Y/%m/%d/00")
        to_str = today.strftime("%Y/%m/%d/23")

    # To avoid duplication of conversation, apply most the latest conversation by using subquery
    query = f"""
WITH LatestRecords AS (
    SELECT
        newimage.BotId.S AS BotId,
        newimage.SK.S AS SK,
        MAX(datehour) AS LatestDateHour
    FROM
        {USAGE_ANALYSIS_DATABASE}.{USAGE_ANALYSIS_TABLE}
    WHERE
        datehour BETWEEN '{from_str}' AND '{to_str}'
        AND Keys.SK.S LIKE CONCAT(Keys.PK.S, '#CONV#%')
    GROUP BY
        newimage.BotId.S,
        newimage.SK.S
),
PreAggregatedData AS (
    SELECT
        d.newimage.BotId.S AS BotId,
        d.newimage.SK.S AS SK,
        d.datehour,
        d.newimage.TotalPrice.N AS TotalPrice
    FROM
        {USAGE_ANALYSIS_DATABASE}.{USAGE_ANALYSIS_TABLE} d
    WHERE
        datehour BETWEEN '{from_str}' AND '{to_str}'
        AND d.Keys.SK.S LIKE CONCAT(d.Keys.PK.S, '#CONV#%')
),
AggregatedData AS (
    SELECT
        p.BotId,
        p.TotalPrice
    FROM
        PreAggregatedData p
    JOIN
        LatestRecords lr ON p.BotId = lr.BotId AND p.SK = lr.SK AND p.datehour = lr.LatestDateHour
)
SELECT
    BotId,
    SUM(TotalPrice) AS TotalPrice
FROM
    AggregatedData
GROUP BY
    BotId
ORDER BY
    TotalPrice DESC
LIMIT {limit};
"""

    logger.debug(query)
    response = await run_athena_query(
        query,
        USAGE_ANALYSIS_DATABASE,
        USAGE_ANALYSIS_WORKGROUP,
        USAGE_ANALYSIS_OUTPUT_LOCATION,
    )
    rows = response["ResultSet"]["Rows"][1:]
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -



packages/blueprints/gen-ai-chatbot/static-assets/chatbot-genai-components/backend/python/app/repositories/usage_analysis.py [220:290]:
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
    assert 1 <= limit <= 1000, "Limit must be between 1 and 1000."

    assert (from_ and to_) or (
        not from_ and not to_
    ), "Both from_ and to_ must be specified or omitted."

    if from_ is not None and to_ is not None:
        from_str = re.sub(r"(\d{4})(\d{2})(\d{2})(\d{2})", r"\1/\2/\3/\4", from_)  # type: ignore
        to_str = re.sub(r"(\d{4})(\d{2})(\d{2})(\d{2})", r"\1/\2/\3/\4", to_)  # type: ignore
    else:
        today = date.today()
        from_str = today.strftime("%Y/%m/%d/00")
        to_str = today.strftime("%Y/%m/%d/23")

    # To avoid duplication of conversation, apply most the latest conversation by using subquery
    query = f"""
WITH LatestRecords AS (
    SELECT
        newimage.PK.S AS UserId,
        newimage.SK.S AS SK,
        MAX(datehour) AS LatestDateHour
    FROM
        {USAGE_ANALYSIS_DATABASE}.{USAGE_ANALYSIS_TABLE}
    WHERE
        datehour BETWEEN '{from_str}' AND '{to_str}'
        AND Keys.SK.S LIKE CONCAT(Keys.PK.S, '#CONV#%')
    GROUP BY
        newimage.PK.S,
        newimage.SK.S
),
PreAggregatedData AS (
    SELECT
        d.newimage.PK.S AS UserId,
        d.newimage.SK.S AS SK,
        d.datehour,
        d.newimage.TotalPrice.N AS TotalPrice
    FROM
        {USAGE_ANALYSIS_DATABASE}.{USAGE_ANALYSIS_TABLE} d
    WHERE
        datehour BETWEEN '{from_str}' AND '{to_str}'
        AND d.Keys.SK.S LIKE CONCAT(d.Keys.PK.S, '#CONV#%')
),
AggregatedData AS (
    SELECT
        p.UserId,
        p.TotalPrice
    FROM
        PreAggregatedData p
    JOIN
        LatestRecords lr ON p.UserId = lr.UserId AND p.SK = lr.SK AND p.datehour = lr.LatestDateHour
)
SELECT
    UserId,
    SUM(TotalPrice) AS TotalPrice
FROM
    AggregatedData
GROUP BY
    UserId
ORDER BY
    TotalPrice DESC
LIMIT {limit};
"""

    logger.debug(query)
    response = await run_athena_query(
        query,
        USAGE_ANALYSIS_DATABASE,
        USAGE_ANALYSIS_WORKGROUP,
        USAGE_ANALYSIS_OUTPUT_LOCATION,
    )
    rows = response["ResultSet"]["Rows"][1:]
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -