It would seem we're each doing what works for us.

I work mainly in MySQL and Teradata (which has tmp tables which are called volatile tables), and I do exactly what you describe when creating complex queries. My metric system is just a way to build those temp tables more rapidly.

I use two main functions to manipulate metrics:

  create_metric(metric_name,{cols_added},join_src,{join_cols},{extra_sql},{indices});

This stores the metric for later use.

  add_metric(metric_name, my_other_table, {my_join_conditions});

This retrieves the metric, and returns a string of (Teradata) SQL in the form:

  CREATE VOLATILE MULTISET TABLE add_metric_name AS (

  SELECT a.*, b.cols_added_1, b.cols_added_2,...,b.cols_added_n
  
  FROM my_other_table a

  LEFT JOIN join_src b

  ON a.my_join_condition_1 = b.join_col_1

  AND a.my_join_condition_2 = b.join_col_2

  ...

  AND a.my_join_condition_n = b.join_col_n

  [If there is extra sql, like where a.condition = X, or group by's like group by 1, 2, it would show up here. SQL here can reference the join columns and table name in an add_metric stmt]

  ) WITH DATA PRIMARY INDEX(indice_1, indice_2) ON COMMIT PRESERVE ROWS;

I can also store entire create tmp table chains as metrics, with the last table appending all of the information from that chain to another source (I do this by storing the chain as preparatory sql, which is run before the create add_metric_name statement.

It also allows me to search all of my metrics on a number of different dimensions: the common name of the information I am adding (metric name), column names, tables, join conditions (particularly useful - It helps you map how you'll get from one metric to another), indices, or any combination of the above. For example I can find all metrics that have the word phone in the metric name and are joinable on user_id.

I'm aware of Oracle's lack of tmp tables. My fiancée has to use Oracle SQL at work, and I quickly discovered its lack of tmp tables when trying to help her solve a SQL issue.