WITH
clause allows you to get a clearer structure by giving a sub-query a name and allowing it to be referenced in multiple places. While this is helpful, I recommend the use of persistent tables to save intermediate results. Instead of one query that does a dozen different things, you can create a sequence of queries, each of which does only one or two things and writes the result to a table with a meaningful name that can be used in several different places later in the script. Doing this is also helpful because it allows the database to optimize the execution plan of queries based on internal statistics.WHERE
statement as well as INNER JOINs with tables that limit your number of records. These filters are often buried deep inside your SQL script, sometimes in multiple different places. Instead, try to create separate tables with the relevant records right at the beginning of your script. For exampleTEMP_SALESSTATS_RELEVANT_CUSTOMERS
TEMP_SALESSTATS_RELEVANT_OFFERS
TEMP_SALESSTATS_RELEVANT_STORES
TEMP_SALESSTATS_RELEVANT_CUSTOMERS
and look at the source table and the filter conditions.CUSTOMER_SALESSTATS
to the table TEMP_SALESSTATS_RELEVANT_CUSTOMERS
from the beginning to see whether you lost any records along the way.