Running Totals

SELECT id,month
 , Amount
 , SUM(Amount) OVER (ORDER BY id) as total_sum
FROM bill

Common Table Expressions

WITH idtempp as (
  SELECT id as id
  FROM id 
  WHERE country = "US"
  AND status = "Y"
)

SELECT *
FROM bill
WHERE id in (SELECT id from idtempp)

Temporary Functions

Temporary functions allow you to modify the data easily without writing huge case statements.

CREATE TEMPORARY FUNCTION get_gender(type varchar) AS (
   CASE WHEN type = "M" THEN "male"
        WHEN type = "F" THEN "female"
        ELSE "n/a"
   END
)
SELECT 
  name,
  get_gender(Type) as gender
FROM bill