report.rb

  from 'batch_views.items i'
  period_field 'SUBSTRING(i.created_at, 1, 10)'
  period_type Report::DatePeriod

  dimensions portal:               'i.country',
             platform:             'i.app_id',
             price_group:          'i.price_groups',
             detailed_price_group: 'i.price_groups_detailed',
             paid_organic:         'u.paid_organic',
             voucher_member:       "IF(u.voucher_campaign IS NOT NULL, 'voucher', 'non-voucher')",
             channel:              'u.channel',
             media_source:         'u.media_source',
             sub_channel:          'u.sub_channel',
             birthyear:            'u.birthyear'

  default_dimensions :metric, :portal

  metrics listings:                     'COUNT(1)',
          successfull_sales_in_1_day:   "SUM(IF(DATEDIFF(i.sold_at, i.created_at) < 1, 1, 0))",
          successfull_sales_in_7_days:  "SUM(IF(DATEDIFF(i.sold_at, i.created_at) < 7, 1, 0))",
          successfull_sales_in_30_days: "SUM(IF(DATEDIFF(i.sold_at, i.created_at) < 30, 1, 0))"

  joins 'inner join batch_views.users u ON i.user_id = u.id AND i.country = u.country'

  conditions -> (context) do
    "i.price IS NOT NULL AND i.country IN (#{context.params[:portals].map { |p| "'#{p}'" }.join(',')})"
  end

  translate :platform, ->(context, platform) do
    Vinted.platform(context.portal, app_id: platform)
  end

  with_result do |result, params|
    result.percentage!(:successfull_sales_in_1_day, :listings, :successfull_sales_rate_in_1_day)
    result.percentage!(:successfull_sales_in_7_days, :listings, :successfull_sales_rate_in_7_days)
    result.percentage!(:successfull_sales_in_30_days, :listings, :successfull_sales_rate_in_30_days)
    result.convert_to_ratio!(params[:report_dimension_ratios])
    result.sort_by_key!([:price_group, :detailed_price_group])
  end