The same prompt, generated twice — then checked against the real warehouse

-- orders_returns
--
-- WHAT WAS ASKED FOR VS. WHAT THE WAREHOUSE HAS
--
-- The request was for orders that were "returned or refunded". Verified against
-- DataHub, this warehouse tracks RETURNS ONLY. There is no refund concept in the
-- catalog: no refunds / credit-memo / chargeback dataset exists, and no
-- refund_amount, refund_date or refund_status field appears on any dataset. The
-- catalog's own "Returns & Refunds" data product (doc: E-Commerce Data Catalog)
-- resolves to exactly two assets -- order_items (return_date) and order_details --
-- confirming refunds are not modelled separately upstream. This model therefore
-- reports returns. The refunded half of the request cannot be answered from the
-- warehouse as it stands; it would need a new source feed from the payments system.
--
-- The requested "date it was returned/refunded" is also not a single value per
-- order. Returns are recorded per LINE ITEM (order_items.return_date), so an order
-- whose items came back on different days has several return dates. This model is
-- one row per order and exposes first/last return date plus the line-item counts
-- needed to distinguish a fully returned order from a partial one.
--
-- SOURCE (fields below all confirmed present in DataHub)
--   order_entry_db.analytics.order_details -- Snowflake table, also a dbt model in
--   the order_entry_db project. Grain: one row per order line item.
--     order_id      NUMBER
--     order_total   FLOAT    order-header value, repeated on every line of the order
--     line_item_id  NUMBER
--     line_total    FLOAT    unit_price * quantity
--     return_date   VARCHAR  date item was returned; NULL = not returned
--     return_status VARCHAR  'Returned' / 'Not Returned', derived from return_date
--
--   order_details is the documented default for analytics. order_details_replica is
--   an exact copy for query load balancing and is not used here. The Postgres
--   order_entry tables are the operational source and are not queried directly.
--
-- NOTES ON THE COLUMNS BELOW
--   order_total is GMV at point of sale. Per the Orders Table doc it is captured at
--   order creation and does NOT deduct returns, so it is the value of the order as
--   placed, not the amount left after the return. returned_item_value (sum of
--   line_total over returned lines) is the closest confirmed proxy for the value
--   sent back, since no refund amount is recorded anywhere.
--
--   return_date is a VARCHAR in both order_items and order_details, so it is cast
--   with try_to_date. Rows with an unparseable value still count as returned, but
--   surface as a NULL date rather than failing the build.
--
--   order_status is deliberately not filtered on. Its schema type (NUMBER, with
--   "1=Pending, 2=Processing, 3=Shipped") disagrees with the Orders Table doc,
--   which describes string statuses ('Cancelled', 'On Hold'). The encoding could
--   not be confirmed, so no cancellation filter is applied. Add one here once the
--   status domain is settled if cancelled orders should be excluded.
 
with order_lines as (
 
    select
        order_id,
        line_item_id,
        order_total,
        line_total,
        return_date,
        try_to_date(return_date) as returned_on
 
    from {{ source('analytics', 'order_details') }}
 
),
 
returns_by_order as (
 
    -- Aggregate every line of the order, not just the returned ones, so that
    -- total_line_items is a true denominator for the return.
    select
        order_id,
 
        -- order_total is constant across the order's lines; max() just picks it.
        max(order_total) as order_total,
 
        min(case when return_date is not null then returned_on end) as first_return_date,
        max(case when return_date is not null then returned_on end) as last_return_date,
 
        count(distinct case when return_date is not null then line_item_id end) as returned_line_items,
        count(distinct line_item_id) as total_line_items,
 
        sum(case when return_date is not null then line_total else 0 end) as returned_item_value
 
    from order_lines
    group by order_id
 
)
 
select
    order_id,
    order_total,
    first_return_date,
    last_return_date,
    returned_line_items,
    total_line_items,
    returned_item_value
 
from returns_by_order
where returned_line_items > 0
order by first_return_date desc, order_id