Method test_nonrecursive

test/sql/test_cte.py:39–97

(self)

    __dialect__ = "default_enhanced"

    def test_nonrecursive(self):
        orders = table(
            "orders",
            column("region"),
            column("amount"),
            column("product"),
            column("quantity"),
        )

        regional_sales = (
            select(
                orders.c.region,
                func.sum(orders.c.amount).label("total_sales"),
            )
            .group_by(orders.c.region)
            .cte("regional_sales")
        )

        top_regions = (
            select(regional_sales.c.region)
            .where(
                regional_sales.c.total_sales
                > select(
                    func.sum(regional_sales.c.total_sales) // 10
                ).scalar_subquery()
            )
            .cte("top_regions")
        )

        s = (
            select(
                orders.c.region,
                orders.c.product,
                func.sum(orders.c.quantity).label("product_units"),
                func.sum(orders.c.amount).label("product_sales"),
            )
            .where(orders.c.region.in_(select(top_regions.c.region)))
            .group_by(orders.c.region, orders.c.product)
        )

        # needs to render regional_sales first as top_regions
        # refers to it
        self.assert_compile(
            s,
            "WITH regional_sales AS (SELECT orders.region AS region, "
            "sum(orders.amount) AS total_sales FROM orders "
            "GROUP BY orders.region), "
            "top_regions AS (SELECT "
            "regional_sales.region AS region FROM regional_sales "
            "WHERE regional_sales.total_sales > "
            "(SELECT FLOOR(sum(regional_sales.total_sales) / :sum_1) AS "
            "anon_1 FROM regional_sales)) "
            "SELECT orders.region, orders.product, "
            "sum(orders.quantity) AS product_units, "
            "sum(orders.amount) AS product_sales "
            "FROM orders WHERE orders.region "
            "IN (SELECT top_regions.region FROM top_regions) "
            "GROUP BY orders.region, orders.product",