Marketplace AI/ML Applications and Product Vision Questions

-- baseline computed over training timeframe
CREATE TABLE baseline_stats AS
SELECT
  'feature1' AS feature,
  AVG(feature1) AS mean,
  VAR_SAMP(feature1) AS var,
  MIN(feature1) AS min_v,
  MAX(feature1) AS max_v
FROM events
WHERE event_time BETWEEN '2024-01-01' AND '2024-06-30';

SELECT
  DATE(event_time) AS day,
  AVG(feature1) AS mean,
  VAR_SAMP(feature1) AS var,
  COUNT(*) AS n
FROM events
WHERE event_type='serve'
GROUP BY DATE(event_time);

-- example bucketized counts
SELECT
  period, -- 'baseline' or day
  width_bucket(feature1, 0, 100, 20) AS bucket,
  COUNT(*) AS cnt
FROM (
  SELECT feature1, 'baseline' AS period FROM events WHERE event_time BETWEEN '2024-01-01' AND '2024-06-30'
  UNION ALL
  SELECT feature1, CAST(DATE(event_time) AS TEXT) FROM events WHERE event_type='serve' AND event_time >= '2024-07-01'
) t
GROUP BY period, bucket;

-- join daily stats to baseline
SELECT d.day,
  (d.mean - b.mean) / SQRT(b.var / b_n + d.var / d.n) AS mean_zscore
FROM daily_stats d
JOIN baseline_stats b ON b.feature='feature1'
CROSS JOIN (SELECT COUNT(*) AS b_n FROM events WHERE event_time BETWEEN '2024-01-01' AND '2024-06-30') bn;

-- baseline category probs
WITH base AS (
  SELECT feature2 AS cat, COUNT(*)::float / SUM(COUNT(*)) OVER() AS p
  FROM events
  WHERE event_time BETWEEN '2024-01-01' AND '2024-06-30'
  GROUP BY cat
),
daily AS (
  SELECT DATE(event_time) AS day, feature2 AS cat, COUNT(*)::float / SUM(COUNT(*)) OVER(PARTITION BY DATE(event_time)) AS q
  FROM events
  WHERE event_type='serve' AND event_time >= '2024-07-01'
  GROUP BY DATE(event_time), cat
)
SELECT d.day, SUM( COALESCE(d.q,1e-9) * LN( COALESCE(d.q,1e-9) / COALESCE(b.p,1e-9) ) ) AS kl_divergence
FROM (
  SELECT day, cat, q FROM daily
) d
LEFT JOIN base b ON d.cat = b.cat
GROUP BY d.day;

CREATE TABLE daily_drift_summary AS
SELECT
  d.day,
  d.mean AS feature1_mean,
  b.mean AS baseline_mean,
  ABS(d.mean - b.mean) / NULLIF(b.mean,0) AS mean_rel_change,
  mean_zscore,
  kl_divergence,
  CASE WHEN ABS(d.mean - b.mean) / NULLIF(b.mean,0) > 0.1 OR ABS(mean_zscore) > 3 OR kl_divergence > 0.1 THEN TRUE ELSE FALSE END AS alert
FROM daily_stats d
JOIN baseline_stats b ON b.feature='feature1'
JOIN kl_table k ON k.day = d.day;

import math
from typing import List

def compute_ndcg_at_k(scores: List[float], relevances: List[int], k: int) -> float:
    """
    Compute NDCG@k for a single query.
    - scores: predicted scores (higher better)
    - relevances: integer relevance labels (higher better)
    - k: cutoff
    Deterministic tie-break: use original index ascending.
    """
    if len(scores) != len(relevances):
        raise ValueError("scores and relevances must have same length")
    n = len(scores)
    if n == 0 or k <= 0:
        return 0.0

    # attach index for deterministic ties
    items = list(enumerate(zip(scores, relevances)))  # (idx, (score, rel))

    # sort by score desc, then idx asc
    items_sorted_by_score = sorted(items, key=lambda x: (-x[1][0], x[0]))
    top_k = items_sorted_by_score[:min(k, n)]
    dcg = 0.0
    for rank, (_, (_, rel)) in enumerate(top_k, start=1):
        dcg += (2**rel - 1) / math.log2(rank + 1)

    # ideal ranking: sort by relevance desc then idx asc
    items_sorted_by_rel = sorted(items, key=lambda x: (-x[1][1], x[0]))
    ideal_top_k = items_sorted_by_rel[:min(k, n)]
    idcg = 0.0
    for rank, (_, (_, rel)) in enumerate(ideal_top_k, start=1):
        idcg += (2**rel - 1) / math.log2(rank + 1)

    return dcg / idcg if idcg > 0 else 0.0

import tensorflow as tf
base = tf.keras.applications.EfficientNetB0(include_top=False, weights='imagenet', input_shape=(224,224,3))
x = tf.keras.layers.GlobalAveragePooling2D()(base.output)
x = tf.keras.layers.Dropout(0.3)(x)
out = tf.keras.layers.Dense(1, activation='sigmoid')(x)
model = tf.keras.Model(base.input, out)