10G_bára_sql dotazy_2

----- vypočítej avg / median time frame window trialistů ---------

AVG:
select avg(time_frame_window) from v_marketing_reporting_faster where rownumber = '1' and email in ('callsworth11.federico@gmail.com',
'under0work@gmail.com',
.... /// A DALŠÍ TRIALISTI ////
-> ukončit závorkou, vymazat ","

MEDIAN:
select median(time_frame_window) from v_marketing_reporting_faster where email in ('callsworth11.federico@gmail.com',
'under0work@gmail.com',
.... /// A DALŠÍ TRIALISTI //// )

----- vypočítej avg / median time frame window těch co zaplatili order ---------

select avg(time_frame_window) from v_marketing_reporting_faster where rownumber = '1' and email not in ('callsworth11.federico@gmail.com',
'under0work@gmail.com',
.... /// A DALŠÍ TRIALISTI //// -> zde pracujeme s log operandem "NOT" tak tedy zapsat všechny emaily trialistů

-------- DALŠÍ SQL MASTEROVINKY ------------------

//// vyber nejlepší countries za dané časové období .. DÁ SE BRÁT JAKO LTV //////

SELECT DISTINCT short_name AS country,
                sum(sum_total)
FROM v_marketing_reporting_faster
WHERE first_payment_date >= '2017-08-01'
  AND first_payment_date <= '2017-08-31'
GROUP BY short_name
ORDER BY sum(sum_total) DESC

////

///////// vyber nejlepší zdroje a medium za dané časové období //////
SELECT DISTINCT source, medium,
                sum(sum_total)
FROM v_marketing_reporting_faster
WHERE first_payment_date >= '2017-08-01'
  AND first_payment_date <= '2017-08-31'
GROUP BY source, medium
ORDER BY sum(sum_total) DESC

////

///////// vyber jen cpc zdroje za dané časové období .. DÁ SE BRÁT JAKO LTV cpc zákošů //////

SELECT *
FROM v_marketing_reporting_faster
WHERE first_payment_date >= '2017-08-01'
  AND first_payment_date <= '2017-08-31'
  AND medium = 'cpc'

/////


///////// vyber země bez trialů (jen ty s order) //////
SELECT DISTINCT short_name AS country,
                sum(sum_total)
FROM v_marketing_reporting_faster
WHERE first_payment_date >= '2017-08-01'
  AND first_payment_date <= '2017-08-31'
  AND sum_total > 130
GROUP BY short_name
ORDER BY sum(sum_total) DESC

////

///// KOLÁČ od krise - source, medium, sum_total //////

SELECT source, medium,
                sum(sum_total)
FROM v_marketing_reporting_faster
WHERE first_payment_date >= '2017-08-01'
  AND first_payment_date <= '2017-08-31'
GROUP BY source, medium
ORDER BY sum(sum_total) DESC