# A dataset is a logical object that represents a set of related facts, attributes, and attribute labels.
# Datasets are basic organization units of a logical data model.
# Read more about Datasets:
# https://www.gooddata.com/developers/cloud-native/doc/cloud/model-data/concepts/dataset/

type: dataset
id: ds_return_reasons

sql: |
  with cte as (
  select
  	drr.tenant_id as drr__tenant_id,
  	dr.key as dr__key,
  	drr.key as drr__key,
  	drr.reason_category as drr__reason_category,
  	drr.reason_category_full as drr__reason_category_full,
  	drr.reason as drr__reason,
  	fol.variant_key as fol__variant_key,
  	dv.variant_key as dv__variant_key,
  	dv.product_key as dv__product_key,
  	dp.product_euid as dp__product_euid,
  	ds.style_key as ds__style_key
  from
  	dim_return_reasons drr,
  	dim_returns dr,
  	fct_order_lines fol,
  	dim_variants dv,
  	dim_products dp,
  	dim_styles ds
  where
  	drr.key = dr.key
  	and dr.key = fol.return_key
  	and fol.variant_key = dv.variant_key
  	and dv.product_key = dp.product_key
  	and dp.style_key = ds.style_key
  	-- and dp.product_key = '001c27fd30ddc0a0a23212b912838de4'
  ),
  counts_reason_level as (
  select
  	cte.drr__tenant_id,
  	cte.dr__key,
  	cte.dp__product_euid,
  	cte.dv__product_key,
  	cte.drr__key,
  	cte.drr__reason_category,
  	cte.drr__reason_category_full,
  	cte.drr__reason,
  	count(cte.drr__key) over (partition by drr__tenant_id,
  	dr__key,
  	drr__reason_category ) as cnt_r_in_rtn,
  	count(cte.drr__key) over (partition by drr__tenant_id,
  	dr__key ) as cnt_r_in_rtn_rc,
  	COUNT(cte.drr__key) as cnt
  from
  	cte
  group by
  	cte.drr__tenant_id,
  	cte.dr__key,
  	cte.dv__product_key,
  	cte.dp__product_euid,
  	cte.drr__key,
  	cte.drr__reason_category,
  	cte.drr__reason_category_full,
  	cte.drr__reason
  ),
  w_counts_reason_level as (
  select
  	*,
  	(cnt::float / cnt_r_in_rtn::float)::float as w_r_in_rtn
  from
  	counts_reason_level
  order by
  	dr__key,
  	dv__product_key,
  	drr__reason_category,
  	drr__reason),
  counts_reason_category_level as (
  select
  	cte.drr__tenant_id,
  	cte.dr__key,
  	cte.dp__product_euid,
  	cte.dv__product_key,
  	cte.drr__key,
  	cte.drr__reason_category,
  	cte.drr__reason_category_full,
  	count(cte.drr__key) over (partition by drr__tenant_id,
  	dr__key ) as cnt_r_in_rtn_rc,
  	COUNT(cte.drr__key) as cnt
  from
  	cte
  group by
  	cte.drr__tenant_id,
  	cte.dr__key,
  	cte.dv__product_key,
  	cte.dp__product_euid,
  	cte.drr__key,
  	cte.drr__reason_category,
  	cte.drr__reason_category_full
  ),
  w_counts_reason_category_level as (
  select
  	*,
  	cnt_r_in_rtn_rc::float / cnt::float as w_r_in_rtn_rc
  from
  	counts_reason_category_level
  order by
  	dr__key,
  	dv__product_key,
  	drr__reason_category)
  select
  	t1.drr__tenant_id as tenant_id,
  	t1.dr__key as return_key,
  	t1.dv__product_key as product_key,
  	t1.dp__product_euid as product_euid,
  	t1.drr__key as return_reason_key,
  	t1.drr__reason_category as return_reason_category,
  	t1.drr__reason_category_full as return_reason_category_full,
  	t1.drr__reason as return_reason, 
  	t1.w_r_in_rtn as weight_for_calc_at_reason_level,
  	t2.w_r_in_rtn_rc as weight_for_calc_at_reason_category_level
  from
  	w_counts_reason_level as t1,
  	w_counts_reason_category_level as t2
  where
  	t1.dr__key = t2.dr__key

title: ds_return_reasons
description: ds_return_reasons
tags:
  - ds_return_reasons

fields:
  ds_return_reasons.product_euid:
    type: attribute
    source_column: product_euid
    data_type: STRING
    title: Product euid
    description: Product euid
    tags:
      - ds_return_reasons
  ds_return_reasons.tenant_id:
    type: attribute
    source_column: tenant_id
    data_type: STRING
    title: Tenant id
    description: Tenant id
    tags:
      - ds_return_reasons
  return_key:
    type: attribute
    source_column: return_key
    data_type: STRING
    title: Return key
    description: Return key
    tags:
      - ds_return_reasons
  return_reason:
    type: attribute
    source_column: return_reason
    data_type: STRING
    title: Return reason
    description: Return reason
    tags:
      - ds_return_reasons
  return_reason_category:
    type: attribute
    source_column: return_reason_category
    data_type: STRING
    title: Return reason category
    description: Return reason category
    tags:
      - ds_return_reasons
  return_reason_category_full:
    type: attribute
    source_column: return_reason_category_full
    data_type: STRING
    title: Return reason category full
    description: Return reason category full
    tags:
      - ds_return_reasons
  return_reason_key:
    type: attribute
    source_column: return_reason_key
    data_type: STRING
    title: Return reason key
    description: Return reason key
    tags:
      - ds_return_reasons
  weight_for_calc_at_reason_category_level:
    type: fact
    source_column: weight_for_calc_at_reason_category_level
    data_type: NUMERIC
    title: Weight for calc at reason category level
    description: Weight for calc at reason category level
    tags:
      - ds_return_reasons
  weight_for_calc_at_reason_level:
    type: fact
    source_column: weight_for_calc_at_reason_level
    data_type: NUMERIC
    title: Weight for calc at reason level
    description: Weight for calc at reason level
    tags:
      - ds_return_reasons

references:
  - dataset: dim_products
    sources:
      - source_column: product_key
        data_type: STRING
        target: dim_products.product_key
