| Line 159... |
Line 159... |
| 159 |
o.status 'order_status', o.total_amount,
|
159 |
o.status 'order_status', o.total_amount,
|
| 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 when (pa.value = 3000 and p.gatewayId = 4 and (o.cod is null or o.cod = 0)) then 3001 else pa.value end 'payment_method',
|
| 165 |
upper(trim(t.coupon_code)) as coupon_code,
|
165 |
upper(trim(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
|