Read Primitives — Timeseries Trend Analysis

Read Primitives benchmark · all Read Primitives queries

Time series aggregation with month-over-month analysis

Representative SQL

Rendered for DataFusion with default parameters.

WITH monthly_totals AS (
  SELECT
    DATE_TRUNC('MONTH', o_orderdate) AS order_month,
    COUNT(*) AS order_count,
    SUM(o_totalprice) AS monthly_revenue,
    AVG(o_totalprice) AS avg_order_value,
    EXTRACT(EPOCH FROM DATE_TRUNC('MONTH', o_orderdate)) AS month_epoch
  FROM orders
  WHERE
    o_orderdate >= CAST('1995-01-01' AS DATE)
    AND o_orderdate < CAST('1997-01-01' AS DATE)
  GROUP BY
    DATE_TRUNC('MONTH', o_orderdate)
), monthly_with_lag AS (
  SELECT
    order_month,
    order_count,
    monthly_revenue,
    avg_order_value,
    month_epoch,
    LAG(monthly_revenue, 1) OVER (ORDER BY order_month) AS prev_month_revenue
  FROM monthly_totals
)
SELECT
  order_month,
  order_count,
  monthly_revenue,
  avg_order_value,
  REGR_SLOPE(monthly_revenue, month_epoch) OVER () AS revenue_trend_slope,
  prev_month_revenue,
  (
    monthly_revenue - prev_month_revenue
  ) / NULLIF(prev_month_revenue, 0) * 100 AS mom_growth_pct
FROM monthly_with_lag
ORDER BY
  order_month

Representative DataFrame

Expression family (Polars, PySpark, DataFusion), default parameters.

def timeseries_trend_analysis_expression_impl(ctx: DataFrameContext) -> Any:
    import polars as pl

    monthly = (
        ctx.get_table("orders")
        .native.filter((pl.col("o_orderdate") >= date(1995, 1, 1)) & (pl.col("o_orderdate") < date(1997, 1, 1)))
        .with_columns(pl.col("o_orderdate").dt.truncate("1mo").alias("order_month"))
        .group_by("order_month")
        .agg(
            pl.len().alias("order_count"),
            pl.col("o_totalprice").sum().alias("monthly_revenue"),
            pl.col("o_totalprice").mean().alias("avg_order_value"),
        )
        .sort("order_month")
        .with_columns((pl.col("order_month").cast(pl.Int64) * 86400).cast(pl.Float64).alias("month_epoch"))
    )
    slope = pl.cov("month_epoch", "monthly_revenue", ddof=0) / pl.col("month_epoch").var(ddof=0)
    return (
        monthly.with_columns(slope.alias("revenue_trend_slope"))
        .with_columns(pl.col("monthly_revenue").shift(1).alias("prev_month_revenue"))
        .with_columns(
            pl.when(pl.col("prev_month_revenue") == 0)
            .then(None)
            .otherwise((pl.col("monthly_revenue") - pl.col("prev_month_revenue")) / pl.col("prev_month_revenue") * 100)
            .alias("mom_growth_pct")
        )
        .select(
            "order_month",
            "order_count",
            "monthly_revenue",
            "avg_order_value",
            "revenue_trend_slope",
            "prev_month_revenue",
            "mom_growth_pct",
        )
        .sort("order_month")
    )

Get the exact query for your run

The blocks above are representative. To get the precise statement a specific platform runs at a specific scale, substitute your own values:

from benchbox import ReadPrimitives

bench = ReadPrimitives(scale_factor=0.01)            # your scale factor
queries = bench.get_queries(dialect="datafusion")   # your platform's dialect; drop dialect= for native SQL
print(queries['timeseries_trend_analysis'])

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

print(get_dataframe_render('read_primitives', 'timeseries_trend_analysis', family="expression").source)  # or family="pandas"

Source

Query text: benchbox/core/read_primitives/queries.py