| Line 160... |
Line 160... |
| 160 |
datediff(o.created_timestamp, '20101231') as date_id, hour(o.created_timestamp) as order_hour,
|
160 |
datediff(o.created_timestamp, '20101231') as date_id, hour(o.created_timestamp) as order_hour,
|
| 161 |
o.transaction_id, l.item_id, l.quantity,
|
161 |
o.transaction_id, l.item_id, l.quantity,
|
| 162 |
case when unit_price<2000 then 0 when unit_price < 5000 then 1 when unit_price < 10000 then 2 when unit_price < 15000 then 3 when unit_price <20000 then 4 when unit_price < 50000 then 5 else 6 end as price_range,
|
162 |
case when unit_price<2000 then 0 when unit_price < 5000 then 1 when unit_price < 10000 then 2 when unit_price < 15000 then 3 when unit_price <20000 then 4 when unit_price < 50000 then 5 else 6 end as price_range,
|
| 163 |
l.transfer_price, p.gatewayId,
|
163 |
l.transfer_price, p.gatewayId,
|
| 164 |
case when pa.value is null then case when p.gatewayId = 1 then 2099 when p.gatewayId = 2 then 1099 else null end else pa.value end 'payment_method',
|
164 |
case when pa.value is null then case when p.gatewayId = 1 then 2099 when p.gatewayId = 2 then 1099 else null end else pa.value end 'payment_method',
|
| 165 |
t.coupon_code,
|
165 |
upper(t.coupon_code) as coupon_code,
|
| 166 |
case when t.session_source is null then 'Unknown' when t.session_source like 'DIRECT%' then 'Direct' when t.session_source like 'ORGANIC%' then 'Organic' when t.session_source like 'PAID%' then 'Paid' else 'Referer' end as session_source_type,
|
166 |
case when t.session_source is null then 'Unknown' when t.session_source like 'DIRECT%' then 'Direct' when t.session_source like 'ORGANIC%' then 'Organic' when t.session_source like 'PAID%' then 'Paid' else 'Referer' end as session_source_type,
|
| 167 |
SUBSTRING_INDEX(REPLACE(REPLACE(SUBSTR(session_source, LOCATE('http://', LCASE(session_source)), LENGTH(session_source)), 'http://', ''), 'www.', ''), '/', 1) as session_source_url,
|
167 |
SUBSTRING_INDEX(REPLACE(REPLACE(SUBSTR(session_source, LOCATE('http://', LCASE(session_source)), LENGTH(session_source)), 'http://', ''), 'www.', ''), '/', 1) as session_source_url,
|
| 168 |
case when datediff(t.session_start_time, u.source_start_time) > 30 then 0 when datediff(t.session_start_time, u.source_start_time) > 15 then 1
|
168 |
case when datediff(t.session_start_time, u.source_start_time) > 30 then 0 when datediff(t.session_start_time, u.source_start_time) > 15 then 1
|
| 169 |
when datediff(t.session_start_time, u.source_start_time) > 7 then 2 when datediff(t.session_start_time, u.source_start_time) > 2 then 3
|
169 |
when datediff(t.session_start_time, u.source_start_time) > 7 then 2 when datediff(t.session_start_time, u.source_start_time) > 2 then 3
|
| 170 |
when datediff(t.session_start_time, u.source_start_time) > 1 then 4 else 5 end as days_to_purchase,
|
170 |
when datediff(t.session_start_time, u.source_start_time) > 1 then 4 else 5 end as days_to_purchase,
|