with temporary as (
select mch_nm,count(distinct cust_uid) as customer,sum(trx_amt) as amount
from mt_trx_rcd1
group by mch_nm
)
select mch_nm,
round(amount/customer,4) as avg_spending,
(case when amount/customer<100 then "低档"
when amount/customer>=100 and amount/customer <300 then "中档"
else "高档" end) as label
from temporary
order by amount/customer asc

with temporary as (
select mch_nm,count(distinct cust_uid) as customer,sum(trx_amt) as amount
from mt_trx_rcd1
group by mch_nm
)
select mch_nm,
round(amount/customer,4) as avg_spending,
(case when amount/customer<100 then "低档"
when amount/customer>=100 and amount/customer <300 then "中档"
else "高档" end) as label
from temporary

with temporary as (
select mch_nm,count(distinct cust_uid) as customer,sum(trx_amt) as amount
from mt_trx_rcd1
group by mch_nm
)
select mch_nm,
round(amount/customer,4) as avg_spending,
(case when amount/customer<100 then "低档"
when 100<=amount/customer<300 then "中档"
else "高档" end) as label
from temporary

with table1 as(
select cust_uid,count(distinct mch_typ2) as types
from mt_trx_rcd1
group by cust_uid
)
select cust_uid,
(case when types>=7 then 1 else 0 end) as label
from table1

select
count(search_tm) as total_searches,
count(click_page_id)as total_clicks,
round(count(click_page_id) * 100/count(search_tm),2) as click_rate
from 
jx_query_rcd as j1
left join
jx_click_rcd as j2
on j1.session_id=j2.session_id

with table_temp as (
select j1.usr_id as usr1,j2.usr_id as usr2,key_word
from 
jx_query_rcd as j1 
left join
jx_click_rcd as j2
on j1.session_id=j2.session_id 
)
select key_word,
count(distinct usr1) as total_search_users,
count(distinct usr2) as users_reached_product_page,
round(count(distinct usr2)*100/count(distinct usr1),2) as uv_conversion_rate
from table_temp
group by key_word
order by uv_conversion_rate desc
limit 10

with table_temp as (
select j1.usr_id as usr1,j2.usr_id as usr2,key_word
from 
jx_query_rcd as j1 
left join
jx_click_rcd as j2
on j1.session_id=j2.session_id 
)
select key_word,
count(distinct usr1) as total_search_users,
count(distinct usr2) as users_reached_product_page,
round(count(distinct usr1)*100/count(distinct usr2),2) as uv_conversion_rate
from table_temp
group by key_word
order by uv_conversion_rate desc
limit 10

with table_temp as (
select j1.usr_id as usr1,j2.usr_id as usr2,key_word
from 
jx_query_rcd as j1 
left join
jx_click_rcd as j2
on j1.session_id=j2.session_id 
)
select key_word,
count(distinct usr1) as total_search_users,
count(distinct usr2) as users_reached_product_page,
round(count(distinct usr1)*100/count(distinct usr2),2) as uv_conversion_rate
from table_temp
group by key_word

select count(distinct j1.usr_id) astotal_search_users,
count(distinct j2.usr_id)as users_reached_product_page,
round(count(distinct j2.usr_id)*100/count(distinct j1.usr_id),2)
from 
jx_query_rcd as j1
left join 
jx_click_rcd as j2
on j1.session_id=j2.session_id

select count(distinct j1.usr_id) astotal_search_users,
count(distinct j2.usr_id)as users_reached_product_page,
round(count(distinct j2.usr_id)/count(distinct j1.usr_id),2)
from 
jx_query_rcd as j1
left join 
jx_click_rcd as j2
on j1.session_id=j2.session_id

select count(distinct j1.usr_id) astotal_search_users,
count(distinct j2.usr_id)as users_reached_product_page,
round(count(distinct j2.usr_id)/count(distinct j1.usr_id),2)
from 
jx_query_rcd as j1
left join 
jx_click_rcd as j2
on j1.usr_id=j2.usr_id

select count(distinct j1.usr_id) astotal_search_users,
count(distinct j2.usr_id)as users_reached_product_page,
round(count(distinct j2.usr_id)*100/count(distinct j1.usr_id),2)
from 
jx_query_rcd as j1
left join 
jx_click_rcd as j2
on j1.usr_id=j2.usr_id

with table_contemp as (
select user_id,product_type,
rank()over(partition by user_id order by purchase_time asc) as rk,
(case when product_type='Mac' then 1 else 0 end) as tag
from apple_pchs_rcd
)
select distinct(user_id),
tag
from table_contemp
where rk=1

SELECT 
    user_id,
    CASE 
        WHEN product_type = 'Mac' THEN 1
        ELSE 0
    END AS tag
FROM (
    SELECT 
        user_id,
        product_type,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY purchase_time ASC) AS rn
    FROM apple_pchs_rcd
) AS subquery
WHERE rn = 1
ORDER BY user_id;

with table_contemp as (
select user_id,product_type,
rank()over(partition by user_id order by purchase_time asc) as rk
from apple_pchs_rcd
)
select distinct(user_id),
(case when rk='1' and product_type='Mac' then 1 else 0 end) as tag
from table_contemp

with table_contemp as (
select user_id,product_type,
rank()over(partition by user_id order by purchase_time asc) as rk
from apple_pchs_rcd
)
select user_id,
(case when rk='1' and product_type='Mac' then 1 else 0 end) as tag
from table_contemp

select
month,
count(order_id) as total_orders,
count(distinct user_id) as unique_users,
round(count(order_id) /count(distinct user_id),2) as avg_orders_per_user
from (
select date_format(purchase_time,'%Y-%m') as month,
order_id,
user_id
from apple_pchs_rcd) as table1
group by month

with table_contemp as(
select user_id,product_type,yearweek(purchase_time,1) as week_number
from apple_pchs_rcd
)
select 
week_number,
(case when product_type='iPhone'then 'iPhone' else 'Not iPhone' end) as category,
count(distinct(user_id))as user_count 
from table_contemp
group by category,week_number
order by week_number,category asc

with table_contemp as(
select user_id,product_type,yearweek(purchase_time,1) as week_number
from apple_pchs_rcd
)
select 
week_number,
(case when product_type='iPhone'then 'iPone' else 'Not iPhone' end) as category,
count(distinct(user_id))as user_count 
from table_contemp
group by category,week_number
order by week_number,category asc

with table_contemp as(
select user_id,product_type,yearweek(purchase_time,1) as week_number
from apple_pchs_rcd
)
select 
week_number,
(case when product_type='iPhone'then 'iPone' else 'Not iPone' end) as category,
count(distinct(user_id))as user_count 
from table_contemp
group by category,week_number
order by week_number,category asc