verify_queries.py

from snowflake_client import run_query
import pandas as pd

print("Verifying Dashboard Queries...")

q1_sql = """
SELECT
    DATE_TRUNC('QUARTER', t.TRANSACTION_DATE) as QUARTER_DATE,
    'Q' || EXTRACT(QUARTER FROM t.TRANSACTION_DATE) as QUARTER_LABEL,
    p.CATEGORY,
    SUM(op.PRICE_AFTER_DISCOUNT * op.QUANTITY) as SALES
FROM FINANCE.TRANSACTIONS t
JOIN SHIPBOB.ORDER_PRODUCTS op ON t.ORDER_ID = op.ORDER_ID
JOIN CATALOG.PRODUCT_VARIANTS_CATALOG pvc ON op.VARIANT_ID = pvc.VARIANT_ID
JOIN CATALOG.PRODUCT_CATALOG p ON pvc.PRODUCT_ID = p.PRODUCT_ID
GROUP BY 1, 2, 3
ORDER BY 1, 3
LIMIT 10
"""

q2_sql = """
WITH ProductSales AS (
    SELECT
        DATE_TRUNC('QUARTER', t.TRANSACTION_DATE) as QUARTER_DATE,
        'Q' || EXTRACT(QUARTER FROM t.TRANSACTION_DATE) as QUARTER_LABEL,
        p.PRODUCT_NAME,
        SUM(op.PRICE_AFTER_DISCOUNT * op.QUANTITY) as SALES
    FROM FINANCE.TRANSACTIONS t
    JOIN SHIPBOB.ORDER_PRODUCTS op ON t.ORDER_ID = op.ORDER_ID
    JOIN CATALOG.PRODUCT_VARIANTS_CATALOG pvc ON op.VARIANT_ID = pvc.VARIANT_ID
    JOIN CATALOG.PRODUCT_CATALOG p ON pvc.PRODUCT_ID = p.PRODUCT_ID
    GROUP BY 1, 2, 3
),
Ranked AS (
    SELECT
        *,
        RANK() OVER (PARTITION BY QUARTER_DATE ORDER BY SALES DESC) as RankDesc,
        RANK() OVER (PARTITION BY QUARTER_DATE ORDER BY SALES ASC) as RankAsc
    FROM ProductSales
)
SELECT * FROM Ranked WHERE RankDesc <= 5 OR RankAsc <= 5
ORDER BY QUARTER_DATE, SALES DESC
LIMIT 10
"""

q3_sql = """
SELECT
    cs.AGE_RANGE,
    cs.SEGMENT_NAME,
    SUM(t.TRANSACTION_AMOUNT) as TOTAL_SPEND,
    AVG(t.TRANSACTION_AMOUNT) as AVG_SPEND
FROM FINANCE.TRANSACTIONS t
JOIN SHIPBOB.ORDERS o ON t.ORDER_ID = o.ORDER_ID
JOIN CUSTOMER.CUSTOMER_SUMMARY csum ON o.CUSTOMER_ID = csum.CUSTOMER_ID
JOIN CUSTOMER.DIM_SEGMENTS ds ON csum.SEGMENT_NAME = ds.SEGMENT_NAME
JOIN CUSTOMER.CUSTOMER_SEGMENT cs ON ds.CUSTOMER_SEGMENT_ID = cs.CUSTOMER_SEGMENT_ID
WHERE t.TRANSACTION_DATE >= DATEADD('MONTH', -6, (SELECT MAX(TRANSACTION_DATE) FROM FINANCE.TRANSACTIONS))
GROUP BY 1, 2
ORDER BY 3 DESC
LIMIT 3
"""

print("Debugging Join Chain...")

# 1. Total Transactions with Date Filter
sql_step_1 = "SELECT COUNT(*) as CNT FROM FINANCE.TRANSACTIONS WHERE TRANSACTION_DATE >= DATEADD('MONTH', -6, (SELECT MAX(TRANSACTION_DATE) FROM FINANCE.TRANSACTIONS))"
print("Step 1 (Trans):", run_query(sql_step_1))

# 2. Join to ORDERS
sql_step_2 = """
SELECT COUNT(*) as CNT 
FROM FINANCE.TRANSACTIONS t
JOIN SHIPBOB.ORDERS o ON t.ORDER_ID = o.ORDER_ID
WHERE t.TRANSACTION_DATE >= DATEADD('MONTH', -6, (SELECT MAX(TRANSACTION_DATE) FROM FINANCE.TRANSACTIONS))
"""
print("Step 2 (+Orders):", run_query(sql_step_2))

# 3. Join to CUSTOMER_SUMMARY
sql_step_3 = """
SELECT COUNT(*) as CNT
FROM FINANCE.TRANSACTIONS t
JOIN SHIPBOB.ORDERS o ON t.ORDER_ID = o.ORDER_ID
JOIN CUSTOMER.CUSTOMER_SUMMARY csum ON o.CUSTOMER_ID = csum.CUSTOMER_ID
WHERE t.TRANSACTION_DATE >= DATEADD('MONTH', -6, (SELECT MAX(TRANSACTION_DATE) FROM FINANCE.TRANSACTIONS))
"""
print("Step 3 (+Summary):", run_query(sql_step_3))

# 4. Join to DIM_SEGMENTS
sql_step_4 = """
SELECT COUNT(*) as CNT
FROM FINANCE.TRANSACTIONS t
JOIN SHIPBOB.ORDERS o ON t.ORDER_ID = o.ORDER_ID
JOIN CUSTOMER.CUSTOMER_SUMMARY csum ON o.CUSTOMER_ID = csum.CUSTOMER_ID
JOIN CUSTOMER.DIM_SEGMENTS ds ON csum.SEGMENT_NAME = ds.SEGMENT_NAME
WHERE t.TRANSACTION_DATE >= DATEADD('MONTH', -6, (SELECT MAX(TRANSACTION_DATE) FROM FINANCE.TRANSACTIONS))
"""
print("Step 4 (+DimSegs):", run_query(sql_step_4))

# 5. Join to CUSTOMER_SEGMENT
sql_step_5 = """
SELECT COUNT(*) as CNT
FROM FINANCE.TRANSACTIONS t
JOIN SHIPBOB.ORDERS o ON t.ORDER_ID = o.ORDER_ID
JOIN CUSTOMER.CUSTOMER_SUMMARY csum ON o.CUSTOMER_ID = csum.CUSTOMER_ID
JOIN CUSTOMER.DIM_SEGMENTS ds ON csum.SEGMENT_NAME = ds.SEGMENT_NAME
JOIN CUSTOMER.CUSTOMER_SEGMENT cs ON ds.CUSTOMER_SEGMENT_ID = cs.CUSTOMER_SEGMENT_ID
WHERE t.TRANSACTION_DATE >= DATEADD('MONTH', -6, (SELECT MAX(TRANSACTION_DATE) FROM FINANCE.TRANSACTIONS))
"""
print("Step 5 (+CustSeg - FINAL):", run_query(sql_step_5))