Named Queries - Using WITH Clause¶

Let us understand how to use WITH clause to define a named query.

At times we might have to develop a large query in which same complex logic need to be used multiple times. The query can become cumbersome if you just define the same logic multiple times.
One of the way to mitigate that issue is by providing the name to the logic using WITH clause.
We can only use the names provided to named queries as part of the main query which follows the WITH clause.

Note

In case of frequently used complex and large query, we use named queries while defining the views. We will then use view for reporting purposes.

%load_ext sql

The sql extension is already loaded. To reload it, use:
  %reload_ext sql

%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db

env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db

%%sql

WITH order_details_nq AS (
    SELECT * FROM orders o
        JOIN order_items oi
            on o.order_id = oi.order_item_order_id
) SELECT * FROM order_details_nq LIMIT 10

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
10 rows affected.

order_id	order_date	order_customer_id	order_status	order_item_id	order_item_order_id	order_item_product_id	order_item_quantity	order_item_subtotal	order_item_product_price
1	2013-07-25 00:00:00	11599	CLOSED	1	1	957	1	299.98	299.98
2	2013-07-25 00:00:00	256	PENDING_PAYMENT	2	2	1073	1	199.99	199.99
2	2013-07-25 00:00:00	256	PENDING_PAYMENT	3	2	502	5	250.0	50.0
2	2013-07-25 00:00:00	256	PENDING_PAYMENT	4	2	403	1	129.99	129.99
4	2013-07-25 00:00:00	8827	CLOSED	5	4	897	2	49.98	24.99
4	2013-07-25 00:00:00	8827	CLOSED	6	4	365	5	299.95	59.99
4	2013-07-25 00:00:00	8827	CLOSED	7	4	502	3	150.0	50.0
4	2013-07-25 00:00:00	8827	CLOSED	8	4	1014	4	199.92	49.98
5	2013-07-25 00:00:00	11318	COMPLETE	9	5	957	1	299.98	299.98
5	2013-07-25 00:00:00	11318	COMPLETE	10	5	365	5	299.95	59.99

Error

One cannot use the named queries apart from the query in which it is defined. Following query will fail.

%%sql

SELECT * FROM order_details_nq LIMIT 10

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
(psycopg2.errors.UndefinedTable) relation "order_details_nq" does not exist
LINE 1: SELECT * FROM order_details_nq LIMIT 10
                      ^

[SQL: SELECT * FROM order_details_nq LIMIT 10]
(Background on this error at: http://sqlalche.me/e/13/f405)

%%sql

WITH order_details_nq AS (
    SELECT * FROM orders o
        JOIN order_items oi
            on o.order_id = oi.order_item_order_id
) SELECT order_date,
    order_item_product_id,
    round(sum(order_item_subtotal)::numeric, 2) AS revenue
FROM order_details_nq 
GROUP BY order_date,
    order_item_product_id
ORDER BY order_date,
    revenue DESC
LIMIT 10

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
10 rows affected.

order_date	order_item_product_id	revenue
2013-07-25 00:00:00	1004	10799.46
2013-07-25 00:00:00	957	9599.36
2013-07-25 00:00:00	191	8499.15
2013-07-25 00:00:00	365	7558.74
2013-07-25 00:00:00	1073	6999.65
2013-07-25 00:00:00	1014	6397.44
2013-07-25 00:00:00	403	5589.57
2013-07-25 00:00:00	502	5100.00
2013-07-25 00:00:00	627	2879.28
2013-07-25 00:00:00	226	599.99

%%sql

CREATE OR REPLACE VIEW daily_product_revenue_v
AS
WITH order_details_nq AS (
    SELECT * FROM orders o
        JOIN order_items oi
            on o.order_id = oi.order_item_order_id
) SELECT order_date,
    order_item_product_id,
    round(sum(order_item_subtotal)::numeric, 2) AS revenue
FROM order_details_nq 
GROUP BY order_date,
    order_item_product_id

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.

[]

%%sql

SELECT * FROM daily_product_revenue_v
ORDER BY order_date, revenue DESC
LIMIT 10

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
10 rows affected.

order_date	order_item_product_id	revenue
2013-07-25 00:00:00	1004	10799.46
2013-07-25 00:00:00	957	9599.36
2013-07-25 00:00:00	191	8499.15
2013-07-25 00:00:00	365	7558.74
2013-07-25 00:00:00	1073	6999.65
2013-07-25 00:00:00	1014	6397.44
2013-07-25 00:00:00	403	5589.57
2013-07-25 00:00:00	502	5100.00
2013-07-25 00:00:00	627	2879.28
2013-07-25 00:00:00	226	599.99

Mastering SQL using Postgresql

Named Queries - Using WITH Clause¶