CoffeeShop — Quarterly Trends

CoffeeShop benchmark · all CoffeeShop queries

Quarterly revenue and order growth using FLOOR quarter calculation

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  EXTRACT(YEAR FROM ol.order_date) AS year,
  FLOOR((
    EXTRACT(MONTH FROM ol.order_date) - 1
  ) / 3) + 1 AS quarter,
  COUNT(DISTINCT ol.order_id) AS orders,
  SUM(ol.total_price) AS revenue
FROM order_lines AS ol
WHERE
  EXTRACT(YEAR FROM ol.order_date) BETWEEN 2023 AND 2024
GROUP BY
  year,
  quarter
ORDER BY
  year,
  quarter

Representative DataFrame

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

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

    ol = ctx.get_table("order_lines")
    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)))
        .with_columns((((col("month") - lit(1)) / lit(3)).floor().cast_int() + lit(1)).alias("quarter"))
        .group_by("year", "quarter")
        .agg(
            col("order_id").n_unique().alias("orders"),
            col("total_price").sum().alias("revenue"),
        )
        .sort(["year", "quarter"])
    )

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['TR1'])

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

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

Source

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