| Line 182... |
Line 182... |
| 182 |
<connection>transactionDC_Prod</connection>
|
182 |
<connection>transactionDC_Prod</connection>
|
| 183 |
<sql>SELECT SQL_NO_CACHE o.id 'order_id', o.customer_id, o.customer_pincode,
|
183 |
<sql>SELECT SQL_NO_CACHE o.id 'order_id', o.customer_id, o.customer_pincode,
|
| 184 |
o.status 'order_status', o.total_amount,
|
184 |
o.status 'order_status', o.total_amount,
|
| 185 |
datediff(o.created_timestamp, '20101231') as date_id, hour(o.created_timestamp) as order_hour,
|
185 |
datediff(o.created_timestamp, '20101231') as date_id, hour(o.created_timestamp) as order_hour,
|
| 186 |
o.transaction_id, l.item_id, l.quantity,
|
186 |
o.transaction_id, l.item_id, l.quantity,
|
| 187 |
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,
|
187 |
case when unit_price<250 then 0 when unit_price<500 then 1 when unit_price<1000 then 2 when unit_price<2000 then 3 when unit_price < 5000 then 4 when unit_price < 10000 then 5 when unit_price < 15000 then 6 when unit_price <20000 then 7 when unit_price < 50000 then 8 else 9 end as price_range,
|
| 188 |
l.transfer_price, p.gatewayId,
|
188 |
l.transfer_price, p.gatewayId,
|
| 189 |
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',
|
189 |
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',
|
| 190 |
upper(trim(t.coupon_code)) as coupon_code,
|
190 |
upper(trim(t.coupon_code)) as coupon_code,
|
| 191 |
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,
|
191 |
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,
|
| 192 |
SUBSTRING_INDEX(REPLACE(REPLACE(SUBSTR(session_source, LOCATE('http://', LCASE(session_source)), LENGTH(session_source)), 'http://', ''), 'www.', ''), '/', 1) as session_source_url,
|
192 |
SUBSTRING_INDEX(REPLACE(REPLACE(SUBSTR(session_source, LOCATE('http://', LCASE(session_source)), LENGTH(session_source)), 'http://', ''), 'www.', ''), '/', 1) as session_source_url,
|