04. Time Series Trend Analysis

SELECT
    DATE(opened_at) as date,
    COUNT(*) as ticket_count
FROM `your-project-id.retail_analytics_us.cs_tickets_dummy`
GROUP BY date
ORDER BY date;

# Daily aggregation
daily_counts = tickets.groupby(tickets['opened_at'].dt.date).size()
 
# Visualization
plt.figure(figsize=(12, 5))
daily_counts.plot(kind='line', marker='o')
plt.title('Daily Ticket Volume')
plt.grid(True)
plt.show()

WITH daily_stats AS (
    SELECT DATE(opened_at) as date, COUNT(*) as count
    FROM `your-project-id.retail_analytics_us.cs_tickets_dummy`
    GROUP BY 1
)
SELECT
    date,
    count,
    ROUND(AVG(count) OVER (
        ORDER BY date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ), 2) as ma_7day
FROM daily_stats
ORDER BY date;

# Calculate moving average
rolling_7d = daily_counts.rolling(window=7).mean()
 
# Comparative visualization
plt.figure(figsize=(12, 5))
daily_counts.plot(label='Daily (Raw)', alpha=0.3)
rolling_7d.plot(label='7-Day MA', color='red', linewidth=2)
plt.legend()
plt.show()

WITH weekly_stats AS (
    SELECT
        DATE_TRUNC(opened_at, WEEK(MONDAY)) as week_start,
        COUNT(*) as weekly_count
    FROM `your-project-id.retail_analytics_us.cs_tickets_dummy`
    GROUP BY week_start
)
SELECT
    week_start,
    weekly_count,
    LAG(weekly_count) OVER (ORDER BY week_start) as prev_week_count,
    ROUND(
        (weekly_count - LAG(weekly_count) OVER (ORDER BY week_start)) * 100.0 /
        LAG(weekly_count) OVER (ORDER BY week_start),
        2
    ) as wow_change_pct
FROM weekly_stats
ORDER BY week_start;

# Weekly resampling
weekly_counts = tickets.set_index('opened_at').resample('W-MON').size()
 
# Calculate change rate
wow_pct = weekly_counts.pct_change() * 100
 
print(wow_pct.tail())

WITH stats AS (
    SELECT
        DATE(opened_at) as date,
        COUNT(*) as count
    FROM `your-project-id.retail_analytics_us.cs_tickets_dummy`
    GROUP BY 1
),
bounds AS (
    SELECT
        AVG(count) as avg_val,
        STDDEV(count) as std_val
    FROM stats
)
SELECT
    s.date,
    s.count,
    CASE
        WHEN s.count > (b.avg_val + 2 * b.std_val) THEN 'Anomaly High'
        WHEN s.count < (b.avg_val - 2 * b.std_val) THEN 'Anomaly Low'
        ELSE 'Normal'
    END as status
FROM stats s, bounds b
ORDER BY s.count DESC;

mean = daily_counts.mean()
std = daily_counts.std()
threshold = mean + 2 * std
 
# Filter anomalies
anomalies = daily_counts[daily_counts > threshold]
 
print(f"Mean: {mean:.1f}, Threshold: {threshold:.1f}")
print("Abnormal spike days:")
print(anomalies)