Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- ----- 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 ////
- 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 median(time_frame_window) from v_marketing_reporting_faster where 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 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'
- AND medium = 'cpc'
- GROUP BY source, medium
- ORDER BY sum(sum_total) DESC
- /////
- ///////// 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
Add Comment
Please, Sign In to add comment