hammer_vs_transaction_order

gistfile1.sql

WITH
  transacting_clients AS
  (
    SELECT
      client_number,
      COUNT(*) AS transaction_count,
      AVG(hammer_usd) transaction_avg
    FROM
      `sothebys-data.published.sothebys_transactions`
    WHERE
      client_identity = "buyer" AND hammer_usd IS NOT NULL AND hammer_usd > 0
    GROUP BY
      client_number    
  ),
  
  limited_transacting_clients AS
  (
    SELECT
      client_number
    FROM
      transacting_clients
    WHERE
      transaction_count >= 0
  ),

  transactions_table AS
  (
    SELECT
      b.client_number,
      b.sale_start_date,
      hammer_usd
    FROM
      (
        limited_transacting_clients AS a
        LEFT JOIN
        `sothebys-data.published.sothebys_transactions` AS b
        ON
        a.client_number = b.client_number
      )
    WHERE
      client_identity = "buyer" AND hammer_usd IS NOT NULL AND hammer_usd > 0
  ),
  
  transaction_order_table AS
  (
    SELECT
      client_number,
      sale_start_date,
      ROW_NUMBER() OVER (PARTITION BY client_number ORDER BY sale_start_date, hammer_usd) AS transaction_order,
      hammer_usd
    FROM
      transactions_table
    ORDER BY
      client_number, transaction_order
  )

SELECT
  transaction_order,
  COUNT(*) AS num_transactions,
  ANY_VALUE(median_hammer_usd) AS median_hammer_usd
  -- computing avg_hammer_usd is much easier, write separate query for it
FROM (
  SELECT
    transaction_order,
    PERCENTILE_CONT(hammer_usd,
      0.5) OVER (PARTITION BY transaction_order) AS median_hammer_usd
  FROM
    transaction_order_table)
GROUP BY
  transaction_order
ORDER BY
  transaction_order