function.sql

    CREATE OR REPLACE FUNCTION calculateStoreSalesCycle(_store_id INTEGER)
          RETURNS DECIMAL AS $$
        DECLARE
          dates      TIMESTAMP WITH TIME ZONE ARRAY;
          timedeltas DECIMAL ARRAY;
        BEGIN
          SELECT ARRAY(SELECT platform_created_at
                       INTO dates
                       FROM stores_order
                       WHERE stores_order.store_id = _store_id
                       GROUP BY platform_created_at
                       ORDER BY platform_created_at ASC);
          FOR i IN 1..array_length(dates, 1) LOOP
            SELECT array_append(timedeltas, (EXTRACT(EPOCH FROM dates [i + 1] - dates [i]) / 60 / 60 / 24) :: DECIMAL)
            INTO timedeltas;
            IF i = array_length(dates, 1) - 1
            THEN EXIT;
            END IF;
          END LOOP;
          RETURN (
            SELECT avg(items)
            FROM unnest(timedeltas :: DECIMAL []) items
          );
        END;
        $$
        LANGUAGE plpgsql;
        
        SELECT
          round((SUM("stores_order"."total_price") / COUNT("stores_order"."id")), 2) AS "store_aov",
          calculateStoreSalesCycle(%s)                                                AS "store_cc"
        FROM "stores_order"
        WHERE "stores_order"."store_id" = %s;