SELECT Statement: Row Source Generation

Row source generation for creating dummy virtual data is useful among other things for testing, for virtual data for joining, or for inserting dummy test-data into tables in development environment.

Subquery Factoring: Recursive Subquery Factoring

Recursive subquery factoring makes it possible to multiply for each iteration giving us a product aggregation for a hierarchy.

SELECT Statement: ORDER BY Clause

If you need to dynamically sort either ascending or descending, you can simply reverse the sign for numeric data. Alternatively you can order by two expressions, which can work on all datatypes.

Datetime Functions: FROM_TZ

Use of function FROM_TZ combined with expression AT TIME ZONE can implement a replacement for standard function NEW_TIME that will work with all timezones and not just USA.

Single Row Functions: CEIL

CEIL is useful for "rounding up" a number. If you need to "round up" to nearest multiple of something, you can divide by that something, take CEIL on the result, and then multiply by that something.

Joins: Semijoins

Semijoins are helpful if you just wish to filter on existence of a row in another table (regardless of how many exist) and do not need data from that table.

Single Row Functions: ABS

ABS removes the sign from a number making it very useful for differences, where you do not care if it is higher or lower but just care about the magnitude of the difference.