How to use

  WITH customer_ltv AS (
      SELECT customer_email, SUM(total_price) AS ltv
      FROM public.shopify_orders
      WHERE financial_status IN ('paid', 'partially_paid')
      GROUP BY customer_email
  )
  SELECT
      COUNT(*) AS customers,
      AVG(ltv) AS avg_ltv,
      MIN(ltv) AS min_ltv,
      MAX(ltv) AS max_ltv,
      SUM(ltv) AS total_revenue
  FROM customer_ltv;

with source as (
   select * from {{ source('zendesk', 'zendesk_tickets') }}
),

renamed as (
   select
       ticket_id,
       lower(trim(customer_email))         as customer_email,
       subject,
       description,
       status,
       tags,
       created_at::timestamp               as created_at,
       updated_at::timestamp               as updated_at
   from source
)

select * from renamed

with tickets as (
    select * from {{ ref('stg_zendesk__tickets') }}
),

aggregated as (
    select
        customer_email,
        count(*)                                                    as total_tickets,
        count_if(status = 'open')                                   as open_tickets,
        count_if(status = 'pending')                                as pending_tickets,
        count_if(status = 'solved')                                 as solved_tickets,
        max(created_at)                                             as last_ticket_created_at,
        max(updated_at)                                             as last_ticket_updated_at,
        min(created_at)                                             as first_ticket_created_at,
        datediff('day', min(created_at), max(created_at))           as customer_lifespan_days
    from tickets
    where customer_email is not null
    group by customer_email
)

select * from aggregated

version: 2
sources:
  - name: zendesk
    schema: public
    database: freymcp_demo
    tables:
      - name: zendesk_tickets
        columns:
          - name: ticket_id
            tests: [unique, not_null]
          - name: customer_email
          - name: status
            tests:
              - accepted_values:
                  values: ['open', 'pending', 'solved']

SELECT SUM(total_price)
FROM FREYMCP_DEMO.PUBLIC.shopify_orders
WHERE financial_status = 'pending';
-- $59,610.61

version: 2

models:
  - name: shopify_orders
    description: <source system / grain — e.g. one row per Shopify order>
    columns:
      - name: order_id
        description: Unique order identifier.
        tests:
          - unique
          - not_null
      - name: order_number
        description: Human-readable order number.
      - name: customer_email
        description: Customer email
      - name: created_at
        description: Order creation timestamp (<timezone?>).
      - name: total_price
        description: Order total in <currency>.
      - name: financial_status
        description: "Payment state: paid, partially_paid, pending, refunded."
        tests:
          - accepted_values:
              values: [paid, partially_paid, pending, refunded]
...

CREATE OR REPLACE VIEW PUBLIC.ZENDESK_TICKETS_CLEAN AS
  SELECT
    TICKET_ID, CUSTOMER_EMAIL, SUBJECT, DESCRIPTION,
    STATUS, CREATED_AT, UPDATED_AT,
    ARRAY_SORT(ARRAY_DISTINCT(SPLIT(TAGS, ','))) AS TAGS
  FROM PUBLIC.ZENDESK_TICKETS;