CoffeeShop — Seasonal Revenue

CoffeeShop benchmark · all CoffeeShop queries

Seasonal revenue comparison across regions using CASE WHEN month mapping

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  dl.region,
  CASE
    WHEN EXTRACT(MONTH FROM ol.order_date) IN (12, 1, 2)
    THEN 'Winter'
    WHEN EXTRACT(MONTH FROM ol.order_date) IN (3, 4, 5)
    THEN 'Spring'
    WHEN EXTRACT(MONTH FROM ol.order_date) IN (6, 7, 8)
    THEN 'Summer'
    ELSE 'Fall'
  END AS season,
  SUM(ol.total_price) AS revenue,
  COUNT(DISTINCT ol.order_id) AS orders
FROM order_lines AS ol
JOIN dim_locations AS dl
  ON ol.location_record_id = dl.record_id
WHERE
  EXTRACT(YEAR FROM ol.order_date) BETWEEN 2023 AND 2024
GROUP BY
  dl.region,
  season
ORDER BY
  dl.region,
  season

Representative DataFrame

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

def qc2_expression_impl(ctx: DataFrameContext) -> Any:
    params = get_parameters("QC2")
    start_year = params.get("start_year", 2023)
    end_year = params.get("end_year", 2024)

    ol = ctx.get_table("order_lines")
    dl = ctx.get_table("dim_locations")
    col = ctx.col
    lit = ctx.lit

    return (
        ol.with_columns(
            col("order_date").dt.year().alias("year"),
            col("order_date").dt.month().alias("month"),
        )
        .filter((col("year") >= lit(start_year)) & (col("year") <= lit(end_year)))
        .join(dl, left_on="location_record_id", right_on="record_id")
        .with_columns(
            ctx.when(col("month").is_in([12, 1, 2]))
            .then(lit("Winter"))
            .otherwise(
                ctx.when(col("month").is_in([3, 4, 5]))
                .then(lit("Spring"))
                .otherwise(ctx.when(col("month").is_in([6, 7, 8])).then(lit("Summer")).otherwise(lit("Fall")))
            )
            .alias("season")
        )
        .group_by("region", "season")
        .agg(
            col("total_price").sum().alias("revenue"),
            col("order_id").n_unique().alias("orders"),
        )
        .sort(["region", "season"])
    )

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 CoffeeShop

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

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

print(get_dataframe_render('coffeeshop', 'QC2', family="expression").source)  # or family="pandas"

Source

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