Capturing inputs of a query as a table in SQL

Quite a bit of my work involves working with SQL.

Extracting parameters of an SQL query as a table in the query using the `with` clause has added calm to my queries and made it more manageable.

I give you an example below.

The following query selects trades and events for Bengaluru users executed in Jan'23.

with 
raw_trades as (
Select user_id, name, order_value
from user_trades
where true
	and order_date between '2023-01-01' and '2023-02-01'
	and user_city='BENGALURU'
)
,raw_events as (
Select user_id, event_type, event_data
from user_events
where true
	and event_date between '2023-01-01' and '2023-02-01'
	and user_city='BENGALURU'
)
Select * 
from raw_trades trades 
	inner join raw_events events 
		on trades.user_id=events.user_id

Here, the selection criteria of data selection and city selection are repeated across tables.

A simpler way, that made it more manageable is noting the selection criteria as a table.

This looks like below.

with 
config as (
Select 
	'2023-01-01' as start_date,
	'2023-02-01' as end_date,
	'BENGALURU' as city
)
,raw_trades as (
Select user_id, name, order_value
from user_trades
where true
	and order_date between (Select start_date from config) 
		and (Select end_date from config)
	and user_city= (Select city from config)
)
,raw_events as (
Select user_id, event_type, event_data
from user_events
where true
	and event_date between (Select start_date from config) 
		and (Select end_date from config)
	and user_city= (Select city from config)
)
Select * 
from raw_trades trades 
	inner join raw_events events 
		on trades.user_id=events.user_id 

This approach has the following advantages.

• Separates selection criteria of records from the processing of raw events.

• The update of criteria becomes much more manageable. There is a single place where that warrants change if I move from one month to another, or if I wish to change the range of selection.

Hopefully, this makes your queries more manageable.

Have a great day ahead!