n_orders_per_customer_id.yaml

n_orders_per_customer_id.yaml

feature_name: n_orders
entity_names: [customer_id]		
type: STREAM 		
source: orders 		
query: | 
CREATE OR REPLACE FUNCTION n_orders_per_customer_id( target_uuid varchar )
RETURNS TABLE (customer_id varchar, n_orders number)
as 
$$
  -- get the customer_id for the specific order so we only calculate the feature for 
  -- this customer_id
  WITH needed_id AS
  (
    SELECT id, customer_id
    FROM orders
    WHERE uuid = target_uuid
  )
  SELECT
    customer_id,
    count(o.uuid) AS n_orders   
  FROM
    orders o
    INNER JOIN needed_id on o.id <= needed_id.id AND o.customer_id = needed_id.customer_id
  GROUP BY
    customer_id
$$