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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

%%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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

 * 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)

Copy to clipboard

%%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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

[]

Copy to clipboard

%%sql

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

Copy to clipboard

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

Copy to clipboard

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