ks--ks

data(mushroom)
X = mushroom[, -1]
y = as.numeric(mushroom[, 1])            # конвертировать метки в числа

ks--ks

--convert `$150.00` to cents `15000` query sqlite> 
select replace(replace(Amount, '$', ''), '.', '') from test limit 5;

-- convert `$150.00` to cents `15000` in integer so we can `sum` query sqlite> 
select cast(replace(replace(Amount, '$', ''), '.', '') as integer) 
from test 
limit 5;

-- check column type sqlite> 
select typeof(created_at) 

ks--ks

-- sample data and assign new or returning user type
sampledata as (
select a.user_id, 
    a.created_at
, case 
    when a.created_at between a.firstdate and a.firstdate+27 then 'new' 
        else 'returning' 
        end as type
from (
    select user_id 

ks--ks

WITH actions AS
    (
        SELECT  ca.created_at::date as actdate,
                COUNT(*) AS act,
                CASE when ca.state IN ('CA', 'WA', 'DC', 'NY', 'IL') THEN ca.state ELSE 'other' END AS state,
                CASE WHEN ca.current_medium IN ('source1', 'source2', 'source3') THEN ca.current_medium ELSE 'other' END AS source,
                lower('id-' || ca.created_at::date ||
                      '-' || CASE when ca.state IN ('CA', 'WA', 'DC', 'NY', 'IL') THEN ca.state ELSE 'other' END ||
                      '-' || CASE WHEN ca.current_medium IN ('source1', 'source2', 'source3') THEN ca.current_medium ELSE 'other' END
                ) AS id

ks--ks

WITH

recruits AS (
SELECT recruiter_id AS user_id
	, item_name
	, COUNT(DISTINCT recruited_id) AS recruits
FROM recruit_table
WHERE created_at::DATE BETWEEN '2017-01-01'-180 AND '2017-01-08'
GROUP BY 1, 2
)

ks--ks

WITH actions AS
    (
        SELECT  ca.created_at::date as actdate,
                COUNT(*) AS act,
                CASE when ca.state IN ('CA', 'WA', 'DC', 'NY', 'IL') THEN ca.state ELSE 'other' END AS state,
                CASE WHEN ca.current_medium IN ('source1', 'source2', 'source3') THEN ca.current_medium ELSE 'other' END AS source,
                lower('id-' || ca.created_at::date ||
                      '-' || CASE when ca.state IN ('CA', 'WA', 'DC', 'NY', 'IL') THEN ca.state ELSE 'other' END ||
                      '-' || CASE WHEN ca.current_medium IN ('source1', 'source2', 'source3') THEN ca.current_medium ELSE 'other' END
                ) AS id

ks--ks

action_view as
    (
        SELECT created_at::date AS viewdate,
            COUNT(av.userid) AS act_v,
            CASE when av.state IN ('CA', 'WA', 'DC', 'NY', 'IL') THEN av.state ELSE 'other' END AS state,
            CASE WHEN av.current_medium IN ('source1', 'source2', 'source3') THEN av.current_medium ELSE 'other' END AS source,
            lower('id-' || av.created_at::date ||
                  '-' || CASE when av.state IN ('CA', 'WA', 'DC', 'NY', 'IL') THEN av.state ELSE 'other' END ||
                  '-' || CASE WHEN av.current_medium IN ('source1', 'source2', 'source3') THEN av.current_medium ELSE 'other' END
            ) AS idp

ks--ks

    SELECT av.sapdate             AS date,
           lower(av.state)        AS state,
           lower(av.source)       AS source,
           av.act_v               AS act_view,
           a.act::int             AS act,
    FROM action_view av
    LEFT OUTER JOIN actions a ON av.idp = a.id
    ORDER BY date;

ks--ks

WITH

recruits AS (
SELECT recruiter_id AS user_id
	, item_name
	, COUNT(DISTINCT recruited_id) AS recruits
FROM recruit_table
WHERE created_at::DATE BETWEEN '2017-01-01'-180 AND '2017-01-08'
GROUP BY 1, 2
)

ks--ks

, influence AS (
SELECT user_id 
	, recruit_score 
	, share_score 
	, days_share_score 
	, channel_share_score 
	, NTILE(100) OVER (ORDER BY recruit_score DESC, share_score DESC, days_share_score DESC, channel_share_score DESC)

FROM (
	SELECT a.user_id