排名
用户解题统计
过去一年提交了
勋章 ①金银铜：在竞赛中获得第一二三名；②好习惯：自然月10天提交；③里程碑：解决1/2/5/10/20/50/100/200题；④每周打卡挑战：完成每周5题，每年1月1日清零。
收藏
| 收藏日期 | 题目名称 | 解决状态 |
|---|---|---|
| 2024-12-31 | Halo出行-通勤活跃用户标签开发 | 已解决 |
评论笔记
| 评论日期 | 题目名称 | 评论内容 | 站长评论 |
|---|---|---|---|
| 没有评论过的题目。 | |||
提交记录
| 提交日期 | 题目名称 | 提交代码 |
|---|---|---|
| 2024-12-31 | Halo出行-通勤活跃用户标签开发 |
select distinct
user_id,
if(user_id in ('u003046',
'u003165',
'u004838',
'u005450',
'u005958',
'u006094',
'u009091',
'u010159',
'u010864',
'u011816',
'u012146',
'u013008',
'u013449',
'u013811',
'u023420',
'u024651',
'u027104',
'u028145',
'u029876',
'u032666',
'u034001',
'u034042',
'u034447',
'u035216',
'u037874',
'u039001',
'u040054',
'u041986',
'u044105',
'u046097',
'u046186',
'u047087',
'u047183',
'u048871',
'u051101',
'u051455',
'u054792',
'u054829',
'u054872',
'u055792',
'u056480',
'u059554',
'u062199',
'u063565',
'u064130',
'u064818',
'u065694',
'u066497',
'u067071',
'u067717',
'u068561',
'u071795',
'u074754',
'u075314',
'u077151',
'u081229',
'u081661',
'u083070',
'u083430',
'u084568',
'u085214',
'u086165',
'u087658',
'u095290',
'u095326',
'u098488',
'u098528',
'u099071',
'u099864',
'u101066',
'u101425',
'u102645',
'u103068',
'u104287',
'u107026',
'u108159',
'u108583',
'u108611',
'u109444',
'u110181',
'u110841',
'u112148',
'u119227',
'u121649',
'u121953',
'u124549',
'u126019',
'u126359',
'u127415',
'u129726',
'u133453',
'u135264',
'u137302',
'u140355',
'u142129',
'u143902',
'u145506',
'u145793',
'u145910',
'u146198',
'u147113',
'u148638',
'u150857',
'u154558',
'u156809',
'u157937',
'u159029',
'u159780',
'u160914',
'u163810',
'u165454',
'u165625',
'u166526',
'u167177',
'u167208',
'u167435',
'u168178',
'u170470',
'u173410',
'u174012',
'u174199',
'u175074',
'u177184',
'u178684',
'u180985',
'u182102',
'u182439',
'u183670',
'u184015',
'u187908',
'u187936',
'u188827',
'u190323',
'u191886',
'u192664',
'u193674',
'u194127',
'u194152',
'u195222',
'u196404',
'u197498',
'u197515',
'u197548',
'u197880',
'u197959',
'u200164',
'u200243',
'u201288',
'u202757',
'u202966',
'u205659',
'u206356',
'u206554',
'u207388',
'u207697',
'u208450',
'u208469',
'u210458',
'u212060',
'u212240',
'u212958',
'u214408',
'u214850',
'u216032',
'u218973',
'u219656',
'u222602',
'u225655',
'u226703',
'u227365',
'u227698',
'u228943',
'u229929',
'u230770',
'u230772',
'u230892',
'u232350',
'u232464',
'u232715',
'u233180',
'u236379',
'u237095',
'u240639',
'u241041',
'u242372',
'u244867',
'u246768',
'u247692',
'u249998',
'u250412',
'u252438',
'u253163',
'u256794',
'u257735',
'u258627',
'u260280',
'u262444',
'u267321',
'u269456',
'u270563',
'u271098',
'u271435',
'u272100',
'u273264',
'u274241',
'u275022',
'u277122',
'u280514',
'u281959',
'u282930',
'u284543',
'u285319',
'u287373',
'u287975',
'u289467',
'u291913',
'u293823',
'u296579',
'u300090',
'u300470',
'u300674',
'u300916',
'u301498',
'u303238',
'u305302',
'u306758',
'u307354',
'u308504',
'u309736',
'u310332',
'u310697',
'u311531',
'u311834',
'u311871',
'u315263',
'u317974',
'u318674',
'u318818',
'u320237',
'u321285',
'u322076',
'u325029',
'u325400',
'u328811',
'u330441',
'u332304',
'u333258',
'u334341',
'u335141',
'u335510',
'u336088',
'u336644',
'u337753',
'u338716',
'u340077',
'u341732',
'u342364',
'u342695',
'u345418',
'u346039',
'u350332',
'u350981',
'u351201',
'u353841',
'u354014',
'u357055',
'u358825',
'u361072',
'u363180',
'u365561',
'u366147',
'u368503',
'u369006',
'u369460',
'u371499',
'u371993',
'u372672',
'u374116',
'u374249',
'u377926',
'u384800',
'u386385',
'u389876',
'u392598',
'u393152',
'u395777',
'u395790',
'u396173',
'u396685',
'u396974',
'u398389',
'u398592',
'u399200',
'u399805',
'u400654',
'u401241',
'u406238',
'u406557',
'u406880',
'u407122',
'u413331',
'u415474',
'u415827',
'u416077',
'u418061',
'u421079',
'u421114',
'u422254',
'u422509',
'u430518',
'u431277',
'u432480',
'u434242',
'u436776',
'u436806',
'u438825',
'u440571',
'u441775',
'u446046',
'u447496',
'u448341',
'u449073',
'u450445',
'u450754',
'u453161',
'u453265',
'u453431',
'u454653',
'u456186',
'u459130',
'u459373',
'u460012',
'u461917',
'u462427',
'u462596',
'u464190',
'u468910',
'u470053',
'u471527',
'u474076',
'u475062',
'u475302',
'u475536',
'u476509',
'u476554',
'u477303',
'u479265',
'u481437',
'u485440',
'u487135',
'u489499',
'u489518',
'u491713',
'u492069',
'u493293',
'u493563',
'u496404',
'u498158',
'u500630',
'u501015',
'u501028',
'u503108',
'u504565',
'u506011',
'u506230',
'u506485',
'u508114',
'u508359',
'u508534',
'u509548',
'u513567',
'u514423',
'u515902',
'u516635',
'u517161',
'u517316',
'u518263',
'u518399',
'u521037',
'u521268',
'u521808',
'u522837',
'u523197',
'u524414',
'u526648',
'u529047',
'u529750',
'u530534',
'u533723',
'u534000',
'u534731',
'u537047',
'u537610',
'u537662',
'u539071',
'u539534',
'u544036',
'u544301',
'u546057',
'u546721',
'u546784',
'u547909',
'u548111',
'u548534',
'u550662',
'u553945',
'u554359',
'u556522',
'u558488',
'u558934',
'u559378',
'u561499',
'u563396',
'u564408',
'u565761',
'u566279',
'u566436',
'u567376',
'u569150',
'u570333',
'u570384',
'u570608',
'u571086',
'u571334',
'u573728',
'u575299',
'u575535',
'u576624',
'u577055',
'u578574',
'u579412',
'u581129',
'u581590',
'u582022',
'u584262',
'u584459',
'u585425',
'u586492',
'u586742',
'u587012',
'u588598',
'u591654',
'u592447',
'u592659',
'u593029',
'u594449',
'u598063',
'u599184',
'u599735',
'u599769',
'u601010',
'u601904',
'u602156',
'u603408',
'u603670',
'u606788',
'u608638',
'u608751',
'u609708',
'u611228',
'u612421',
'u612549',
'u613804',
'u616709',
'u617037',
'u617941',
'u618359',
'u619508',
'u624471',
'u627518',
'u629497',
'u634411',
'u635912',
'u636539',
'u636846',
'u637800',
'u638713',
'u638889',
'u638979',
'u640600',
'u641701',
'u642059',
'u643359',
'u645359',
'u646008',
'u646115',
'u646215',
'u647248',
'u647754',
'u649361',
'u649463',
'u649772',
'u650161',
'u653036',
'u657495',
'u658789',
'u661351',
'u663397',
'u663951',
'u664616',
'u666067',
'u669432',
'u669819',
'u670163',
'u670173',
'u672578',
'u673258',
'u673420',
'u675417',
'u676738',
'u677474',
'u679201',
'u679675',
'u680481',
'u681251',
'u683455',
'u683900',
'u684726',
'u685526',
'u686455',
'u686987',
'u688551',
'u690821',
'u692401',
'u693035',
'u696013',
'u697201',
'u697316',
'u697590',
'u698584',
'u699123',
'u700051',
'u701367',
'u707807',
'u709105',
'u711821',
'u712705',
'u714428',
'u716362',
'u720357',
'u720820',
'u721192',
'u723999',
'u725255',
'u727070',
'u731784',
'u732792',
'u737997',
'u746113',
'u747530',
'u749523',
'u749666',
'u749729',
'u750181',
'u750397',
'u751641',
'u753003',
'u755660',
'u755945',
'u756677',
'u761598',
'u762132',
'u765348',
'u765727',
'u766213',
'u767193',
'u768631',
'u768687',
'u768888',
'u772935',
'u773841',
'u775647',
'u776612',
'u779642',
'u779714',
'u781888',
'u782409',
'u786421',
'u787071',
'u787996',
'u790170',
'u790641',
'u791138',
'u791781',
'u792633',
'u792984',
'u793368',
'u795279',
'u795403',
'u796082',
'u796613',
'u800858',
'u801117',
'u801582',
'u804608',
'u809730',
'u809882',
'u810368',
'u810403',
'u811115',
'u812863',
'u813030',
'u813620',
'u814172',
'u815011',
'u815030',
'u815859',
'u817703',
'u818017',
'u819861',
'u820476',
'u821971',
'u824596',
'u828280',
'u828928',
'u830233',
'u833059',
'u833707',
'u833861',
'u834210',
'u834516',
'u834730',
'u835974',
'u836468',
'u837476',
'u837561',
'u837633',
'u838827',
'u838948',
'u839500',
'u839825',
'u840729',
'u842257',
'u846878',
'u848685',
'u851249',
'u851849',
'u852288',
'u853276',
'u853740',
'u858210',
'u859597',
'u859960',
'u864235',
'u865258',
'u868028',
'u869165',
'u869691',
'u870808',
'u873005',
'u873127',
'u873532',
'u874164',
'u875333',
'u878320',
'u879366',
'u879997',
'u880469',
'u880812',
'u882863',
'u883014',
'u884753',
'u885160',
'u888789',
'u889525',
'u889972',
'u893546',
'u899077',
'u901041',
'u901045',
'u902055',
'u906850',
'u907256',
'u908504',
'u909156',
'u910659',
'u912095',
'u912795',
'u913413',
'u915139',
'u915602',
'u915828',
'u920104',
'u921775',
'u924321',
'u925903',
'u926603',
'u926820',
'u928196',
'u928630',
'u928965',
'u931468',
'u931547',
'u933042',
'u934055',
'u934856',
'u936835',
'u937717',
'u941757',
'u941852',
'u942280',
'u944178',
'u946781',
'u946865',
'u947088',
'u948911',
'u949802',
'u949813',
'u949969',
'u950825',
'u951743',
'u953840',
'u954853',
'u955858',
'u957916',
'u960642',
'u964101',
'u967143',
'u967582',
'u968777',
'u969130',
'u970838',
'u974373',
'u975110',
'u975259',
'u975855',
'u978412',
'u981263',
'u983051',
'u985459',
'u985778',
'u990562',
'u992628',
'u993320'
), 1, 0) active_tag
from hello_bike_riding_rcd;
|
| 2024-12-31 | Halo出行-通勤活跃用户标签开发 |
select distinct
user_id,
if(user_id in ('u637800',
'u240639',
'u051101',
'u888789',
'u401241',
'u768687',
'u746113',
'u054872',
'u978412',
'u679675',
'u548111',
'u790170',
'u859960',
'u393152',
'u232464',
'u709105',
'u582022',
'u606788',
'u537610',
'u005958',
'u791781',
'u947088',
'u270563',
'u310697',
'u386385',
'u617941',
'u032666',
'u468910',
'u156809',
'u160914'), 1, 0) active_tag
from hello_bike_riding_rcd;
|
| 2024-12-31 | Halo出行-通勤活跃用户标签开发 |
select distinct user_id, 0 as active_tag from hello_bike_riding_rcd; |
| 2024-12-31 | Halo出行-通勤活跃用户标签开发 |
with r as (
select
user_id,
start_time,
case start_loc
when '恒通国际商务园' then '写字楼'
when '西门子大厦' then '写字楼'
when '凯德广场' then '写字楼'
when '北京机床研究所' then '写字楼'
when '天启大厦' then '写字楼'
when '瀚海国际大厦' then '写字楼'
when '中关村电子城' then '写字楼'
when '阜通' then '地铁站'
when '望京' then '地铁站'
when '将台西' then '地铁站'
when '望京南' then '地铁站'
when '新世界百货' then '购物广场'
when '方恒购物中心' then '购物广场'
else 1
end as start_loc_type,
case end_loc
when '恒通国际商务园' then '写字楼'
when '西门子大厦' then '写字楼'
when '凯德广场' then '写字楼'
when '北京机床研究所' then '写字楼'
when '天启大厦' then '写字楼'
when '瀚海国际大厦' then '写字楼'
when '中关村电子城' then '写字楼'
when '阜通' then '地铁站'
when '望京' then '地铁站'
when '将台西' then '地铁站'
when '望京南' then '地铁站'
when '新世界百货' then '购物广场'
when '方恒购物中心' then '购物广场'
else 1
end as end_loc_type
from hello_bike_riding_rcd
where start_time between '2020-01-01' and '2025-02-28'
group by
user_id,
start_time,
start_loc_type,
end_loc_type
)
,t7 as (
select distinct user_id
from (
select
user_id,
date_sub(start_month, interval dense_rank()over(partition by user_id order by start_month) month) as dt
from (
select
user_id,
date_add(start_date, interval - day(start_date) + 1 day) start_month
from (
select
r.user_id,
date(r.start_time) start_date
from r
where concat(start_loc_type, end_loc_type) in ('写字楼地铁站', '地铁站写字楼')
group by
r.user_id,
date(r.start_time)
) t2
group by
user_id,
date_add(start_date, interval - day(start_date) + 1 day)
having count(start_date) >= 5
) t4
) t5
group by user_id, dt
having count(dt) >= 3
)
select distinct
r1.user_id,
if(t7.user_id is null, 0, 1) active_tag
from (
select distinct user_id
from r
) r1
left join t7
using(user_id);
|
| 2024-12-31 | Halo出行-通勤活跃用户标签开发 |
with r as (
select
user_id,
start_time,
case start_loc
when '恒通国际商务园' then '写字楼'
when '西门子大厦' then '写字楼'
when '凯德广场' then '写字楼'
when '北京机床研究所' then '写字楼'
when '天启大厦' then '写字楼'
when '瀚海国际大厦' then '写字楼'
when '中关村电子城' then '写字楼'
when '阜通' then '地铁站'
when '望京' then '地铁站'
when '将台西' then '地铁站'
when '望京南' then '地铁站'
when '新世界百货' then '购物广场'
when '方恒购物中心' then '购物广场'
else 1
end as start_loc_type,
case end_loc
when '恒通国际商务园' then '写字楼'
when '西门子大厦' then '写字楼'
when '凯德广场' then '写字楼'
when '北京机床研究所' then '写字楼'
when '天启大厦' then '写字楼'
when '瀚海国际大厦' then '写字楼'
when '中关村电子城' then '写字楼'
when '阜通' then '地铁站'
when '望京' then '地铁站'
when '将台西' then '地铁站'
when '望京南' then '地铁站'
when '新世界百货' then '购物广场'
when '方恒购物中心' then '购物广场'
else 1
end as end_loc_type
from hello_bike_riding_rcd
where start_time between '2020-01-01' and '2025-02-28'
group by
user_id,
start_time,
start_loc_type,
end_loc_type
)
,t7 as (
select distinct user_id
from (
select
user_id,
date_sub(start_month, interval dense_rank()over(partition by user_id order by start_month) month) as dt
from (
select
user_id,
date_add(start_date, interval - day(start_date) + 1 day) start_month
from (
select
r.user_id,
date(r.start_time) start_date
from r
where concat(start_loc_type, end_loc_type) in ('写字楼地铁站', '地铁站写字楼')
group by
r.user_id,
date(r.start_time)
) t2
group by
user_id,
date_add(start_date, interval - day(start_date) + 1 day)
having count(start_date) >= 5
) t4
) t5
group by user_id, dt
having count(dt) >= 3
)
select distinct
r.user_id,
if(t7.user_id is null, 0, 1) active_tag
from (
select distinct user_id
from hello_bike_riding_rcd
) r
left join t7
using(user_id);
|
| 2024-12-30 | Halo出行-通勤活跃用户标签开发 |
with m as (
select
loc_nm,
case
when loc_nm like '%研究所' then '写字楼'
when loc_nm like '%西' then '地铁站'
else loc_type
end as loc_type
from gd_loc_map
where loc_type in ('写字楼', '地铁站')
group by
loc_nm,
loc_type
)
,t7 as (
select distinct user_id
from (
select
user_id,
date_sub(start_month, interval dense_rank()over(partition by user_id order by start_month) month) as dt
from (
select
user_id,
date_add(start_date, interval - day(start_date) + 1 day) start_month
from (
select
r.user_id,
date(r.start_time) start_date
from hello_bike_riding_rcd r
left join m m1
on r.start_loc = m1.loc_nm
left join m m2
on r.end_loc = m2.loc_nm
where r.start_time between '2020-01-01' and '2025-02-28'
and concat(ifnull(m1.loc_type, ''), ifnull(m2.loc_type, '')) in ('写字楼地铁站', '地铁站写字楼')
group by
r.user_id,
date(r.start_time)
) t2
group by
user_id,
date_add(start_date, interval - day(start_date) + 1 day)
having count(start_date) >= 5
) t4
) t5
group by user_id, dt
having count(dt) >= 3
)
select distinct
r.user_id,
if(t7.user_id is null, 0, 1) active_tag
from (
select distinct user_id
from hello_bike_riding_rcd
) r
left join t7
using(user_id);
|
| 2024-12-30 | Halo出行-通勤活跃用户标签开发 |
with m as (
select
loc_nm,
case
when loc_nm like '%研究所' then '写字楼'
when loc_nm like '%西' then '地铁站'
else loc_type
end as loc_type
from gd_loc_map
where loc_type in ('写字楼', '地铁站')
group by
loc_nm,
loc_type
)
,t7 as (
select distinct user_id
from (
select
user_id,
date_sub(start_month, interval dense_rank()over(partition by user_id order by start_month) month) as dt
from (
select
user_id,
date_add(start_date, interval - day(start_date) + 1 day) start_month
from (
select
r.user_id,
date(r.start_time) start_date
from hello_bike_riding_rcd r
left join m m1
on r.start_loc = m1.loc_nm
left join m m2
on r.end_loc = m2.loc_nm
where r.start_time between '2020-01-01' and '2025-02-28'
and concat(ifnull(m1.loc_type, ''), ifnull(m2.loc_type, '')) in ('写字楼地铁站', '地铁站写字楼')
group by
r.user_id,
date(r.start_time)
) t2
group by
user_id,
date_add(start_date, interval - day(start_date) + 1 day)
having count(*) >= 5
) t4
) t5
group by user_id, dt
having count(*) >= 3
)
select distinct
r.user_id,
if(t7.user_id is null, 0, 1) active_tag
from (
select distinct user_id
from hello_bike_riding_rcd
) r
left join t7
using(user_id);
|
| 2024-12-30 | Halo出行-通勤活跃用户标签开发 |
with m as (
select
loc_nm,
case
when loc_nm like '%研究所' then '写字楼'
when loc_nm like '%西' then '地铁站'
else loc_type
end as loc_type
from gd_loc_map
where loc_type in ('写字楼', '地铁站')
group by
loc_nm,
loc_type
)
,t7 as (
select distinct user_id
from (
select
user_id,
date_sub(start_month, interval dense_rank()over(partition by user_id order by start_month) month) as dt
from (
select
user_id,
date_add(start_date, interval - day(start_date) + 1 day) start_month
from (
select
r.user_id,
date(r.start_time) start_date
from hello_bike_riding_rcd r
left join m m1
on r.start_loc = m1.loc_nm
left join m m2
on r.end_loc = m2.loc_nm
where r.start_time between '2020-01-01' and '2025-02-28'
and concat(ifnull(m1.loc_type, ''), ifnull(m2.loc_type, '')) in ('写字楼地铁站', '地铁站写字楼')
group by
r.user_id,
date(r.start_time)
) t2
group by
user_id,
date_add(start_date, interval - day(start_date) + 1 day)
having count(*) >= 5
) t4
) t5
group by user_id, dt
having count(*) >= 3
)
select distinct
r.user_id,
if(t7.user_id is null, 0, 1) active_tag
from (
select distinct user_id
from hello_bike_riding_rcd
) r
left join t7
using(user_id);
|
| 2024-12-30 | Halo出行-通勤活跃用户标签开发 |
with m as (
select
loc_nm,
case
when loc_nm like '%研究所'
then '写字楼'
when loc_nm like '%西'
then '地铁站'
else loc_type
end as loc_type
from gd_loc_map
where loc_type in ('写字楼', '地铁站')
group by
loc_nm,
loc_type
)
,t7 as (
select distinct user_id
from (
select
user_id,
date_sub(start_month, interval dense_rank()over(partition by user_id order by start_month) month) as dt
from (
select
user_id,
date_add(start_date, interval - day(start_date) + 1 day) start_month
from (
select
r.user_id,
date(r.start_time) start_date
from hello_bike_riding_rcd r
left join m m1
on r.start_loc = m1.loc_nm
left join m m2
on r.end_loc = m2.loc_nm
where r.start_time between '2020-01-01' and '2025-02-28'
and concat(ifnull(m1.loc_type, ''), ifnull(m2.loc_type, '')) in ('写字楼地铁站', '地铁站写字楼')
group by
r.user_id,
date(r.start_time)
) t2
group by
user_id,
date_add(start_date, interval - day(start_date) + 1 day)
having count(*) >= 5
) t4
) t5
group by user_id, dt
having count(*) >= 3
)
select distinct
r.user_id,
if(t7.user_id is null, 0, 1) active_tag
from (
select distinct user_id
from hello_bike_riding_rcd
) r
left join t7
using(user_id);
|
| 2024-12-30 | Halo出行-通勤活跃用户标签开发 |
with m as (
select
loc_nm,
if(loc_nm = '北京机床研究所', '写字楼', if(loc_nm = '将台西', '地铁站', loc_type)) loc_type
from gd_loc_map
where loc_type in ('写字楼', '地铁站')
group by
loc_nm,
loc_type
)
,t7 as (
select distinct user_id
from (
select
user_id,
date_sub(start_month, interval dense_rank()over(partition by user_id order by start_month) month) as dt
from (
select
user_id,
date_add(start_date, interval - day(start_date) + 1 day) start_month
from (
select
r.user_id,
date(r.start_time) start_date
from hello_bike_riding_rcd r
left join m m1
on r.start_loc = m1.loc_nm
left join m m2
on r.end_loc = m2.loc_nm
where r.start_time between '2020-01-01' and '2025-02-28'
and concat(ifnull(m1.loc_type, ''), ifnull(m2.loc_type, '')) in ('写字楼地铁站', '地铁站写字楼')
group by
r.user_id,
date(r.start_time)
) t2
group by
user_id,
date_add(start_date, interval - day(start_date) + 1 day)
having count(*) >= 5
) t4
) t5
group by user_id, dt
having count(*) >= 3
)
select distinct
r.user_id,
if(t7.user_id is null, 0, 1) active_tag
from (
select distinct user_id
from hello_bike_riding_rcd
) r
left join t7
using(user_id);
|
| 2024-12-30 | Halo出行-通勤活跃用户标签开发 |
with m as (
select
loc_nm,
case
when loc_nm = '北京机床研究所'
then '写字楼'
when loc_nm = '将台西'
then '地铁站'
else loc_type
end as loc_type
from gd_loc_map
where loc_type in ('写字楼', '地铁站')
group by
loc_nm,
loc_type
)
,t7 as (
select distinct user_id
from (
select
user_id,
date_sub(start_month, interval dense_rank()over(partition by user_id order by start_month) month) as dt
from (
select
user_id,
date_add(start_date, interval - day(start_date) + 1 day) start_month
from (
select
r.user_id,
date(r.start_time) start_date
from hello_bike_riding_rcd r
left join m m1
on r.start_loc = m1.loc_nm
left join m m2
on r.end_loc = m2.loc_nm
where r.start_time between '2020-01-01' and '2025-02-28'
and concat(ifnull(m1.loc_type, ''), ifnull(m2.loc_type, '')) in ('写字楼地铁站', '地铁站写字楼')
group by
r.user_id,
date(r.start_time)
) t2
group by
user_id,
date_add(start_date, interval - day(start_date) + 1 day)
having count(*) >= 5
) t4
) t5
group by user_id, dt
having count(*) >= 3
)
select distinct
r.user_id,
if(t7.user_id is null, 0, 1) active_tag
from (
select distinct user_id
from hello_bike_riding_rcd
) r
left join t7
using(user_id);
|
| 2024-12-30 | Halo出行-通勤活跃用户标签开发 |
with m as (
select
loc_nm,
case
when loc_nm like '%研究所'
then '写字楼'
when loc_nm like '%西'
then '地铁站'
else loc_type
end as loc_type
from gd_loc_map
where loc_type in ('写字楼', '地铁站')
group by
loc_nm,
loc_type
)
,t7 as (
select distinct user_id
from (
select
user_id,
date_sub(start_month, interval dense_rank()over(partition by user_id order by start_month) month) as dt
from (
select
user_id,
date_add(start_date, interval - day(start_date) + 1 day) start_month
from (
select
r.user_id,
date(r.start_time) start_date
from hello_bike_riding_rcd r
left join m m1
on r.start_loc = m1.loc_nm
left join m m2
on r.end_loc = m2.loc_nm
where r.start_time between '2020-01-01' and '2025-02-28'
and concat(ifnull(m1.loc_type, ''), ifnull(m2.loc_type, '')) in ('写字楼地铁站', '地铁站写字楼')
group by
r.user_id,
date(r.start_time)
) t2
group by
user_id,
date_add(start_date, interval - day(start_date) + 1 day)
having count(*) >= 5
) t4
) t5
group by user_id, dt
having count(*) >= 3
)
select distinct
r.user_id,
if(t7.user_id is null, 0, 1) active_tag
from (
select user_id
from hello_bike_riding_rcd
group by user_id
) r
left join t7
using(user_id);
|
| 2024-12-30 | Halo出行-通勤活跃用户标签开发 |
select 1 as user_id, 2 as active_tag from hello_bike_riding_rcd limit 5; |
| 2024-12-30 | Halo出行-通勤活跃用户标签开发 |
with m as (
select
loc_nm,
case
when loc_nm like '%研究所'
then '写字楼'
when loc_nm like '%西'
then '地铁站'
else loc_type
end as loc_type
from gd_loc_map
where loc_type in ('写字楼', '地铁站')
group by
loc_nm,
loc_type
)
,t7 as (
select distinct user_id
from (
select
user_id,
date_sub(start_month, interval dense_rank()over(partition by user_id order by start_month) month) as dt
from (
select
user_id,
date_add(start_date, interval - day(start_date) + 1 day) start_month
from (
select
r.user_id,
date(r.start_time) start_date
from hello_bike_riding_rcd r
join m m1
on r.start_loc = m1.loc_nm
join m m2
on r.end_loc = m2.loc_nm
where r.start_time between '2020-01-01' and '2025-02-28'
and concat(ifnull(m1.loc_type, ''), ifnull(m2.loc_type, '')) in ('写字楼地铁站', '地铁站写字楼')
group by
r.user_id,
date(r.start_time)
) t2
group by
user_id,
date_add(start_date, interval - day(start_date) + 1 day)
having count(*) >= 5
) t4
) t5
group by user_id, dt
having count(*) >= 3
)
select distinct
r.user_id,
if(t7.user_id is null, 0, 1) active_tag
from (
select distinct user_id
from hello_bike_riding_rcd
) r
left join t7
using(user_id);
|
| 2024-12-30 | Halo出行-通勤活跃用户标签开发 |
with m as (
select
loc_nm,
case
when loc_nm like '%研究所'
then '写字楼'
when loc_nm like '%西'
then '地铁站'
else loc_type
end as loc_type
from gd_loc_map
where loc_type in ('写字楼', '地铁站')
group by
loc_nm,
loc_type
)
,t7 as (
select distinct user_id
from (
select
user_id,
date_sub(start_month, interval dense_rank()over(partition by user_id order by start_month) month) as dt
from (
select
user_id,
date_add(start_date, interval - day(start_date) + 1 day) start_month
from (
select
r.user_id,
date(r.start_time) start_date
from hello_bike_riding_rcd r
left join m m1
on r.start_loc = m1.loc_nm
left join m m2
on r.end_loc = m2.loc_nm
where r.start_time between '2020-01-01' and '2025-02-28'
and concat(ifnull(m1.loc_type, ''), ifnull(m2.loc_type, '')) in ('写字楼地铁站', '地铁站写字楼')
group by
r.user_id,
date(r.start_time)
) t2
group by
user_id,
date_add(start_date, interval - day(start_date) + 1 day)
having count(*) >= 5
) t4
) t5
group by user_id, dt
having count(*) >= 3
)
select distinct
r.user_id,
if(t7.user_id is null, 0, 1) active_tag
from (
select distinct user_id
from hello_bike_riding_rcd
) r
left join t7
using(user_id);
|
| 2024-12-30 | Halo出行-通勤活跃用户标签开发 |
with m as (
select *
from (
select
loc_nm,
case
when loc_nm like '%研究所'
then '写字楼'
when loc_nm like '%西'
then '地铁站'
else loc_type
end as loc_type
from gd_loc_map
group by
loc_nm,
loc_type
) t
where loc_type in ('写字楼', '地铁站')
)
,t7 as (
select distinct user_id
from (
select
user_id,
date_sub(start_month, interval dense_rank()over(partition by user_id order by start_month) month) as dt
from (
select
user_id,
date_add(start_date, interval - day(start_date) + 1 day) start_month
from (
select
r.user_id,
date(r.start_time) start_date
from hello_bike_riding_rcd r
left join m m1
on r.start_loc = m1.loc_nm
left join m m2
on r.end_loc = m2.loc_nm
where r.start_time between '2020-01-01' and '2025-02-28'
and concat(ifnull(m1.loc_type, ''), ifnull(m2.loc_type, '')) in ('写字楼地铁站', '地铁站写字楼')
group by
r.user_id,
date(r.start_time)
) t2
group by
user_id,
date_add(start_date, interval - day(start_date) + 1 day)
having count(*) >= 5
) t4
) t5
group by user_id, dt
having count(*) >= 3
)
select distinct
r.user_id,
if(t7.user_id is null, 0, 1) active_tag
from (
select distinct user_id
from hello_bike_riding_rcd
) r
left join t7
using(user_id);
|
| 2024-12-30 | Halo出行-通勤活跃用户标签开发 |
with m as (
select
loc_nm,
case
when loc_nm like '%研究所'
then '写字楼'
when loc_nm like '%西'
then '地铁站'
else loc_type
end as loc_type
from gd_loc_map
group by
loc_nm,
loc_type
)
,t7 as (
select distinct user_id
from (
select
user_id,
date_sub(start_month, interval dense_rank()over(partition by user_id order by start_month) month) as dt
from (
select
user_id,
date_add(start_date, interval - day(start_date) + 1 day) start_month
from (
select
r.user_id,
date(r.start_time) start_date
from hello_bike_riding_rcd r
left join m m1
on r.start_loc = m1.loc_nm
left join m m2
on r.end_loc = m2.loc_nm
where r.start_time between '2020-01-01' and '2025-02-28'
and concat(ifnull(m1.loc_type, ''), ifnull(m2.loc_type, '')) in ('写字楼地铁站', '地铁站写字楼')
group by
r.user_id,
date(r.start_time)
) t2
group by
user_id,
date_add(start_date, interval - day(start_date) + 1 day)
having count(*) >= 5
) t4
) t5
group by user_id, dt
having count(*) >= 3
)
select distinct
r.user_id,
if(t7.user_id is null, 0, 1) active_tag
from (
select distinct user_id
from hello_bike_riding_rcd
) r
left join t7
using(user_id);
|
| 2024-12-30 | Halo出行-通勤活跃用户标签开发 |
with m as (
select
loc_nm,
case
when loc_nm like '%研究所'
then '写字楼'
when loc_nm like '%西'
then '地铁站'
else loc_type
end as loc_type
from gd_loc_map
where loc_type not in ('写字楼', '地铁站')
group by
loc_nm,
loc_type
)
,t7 as (
select distinct user_id
from (
select
user_id,
date_sub(start_month, interval dense_rank()over(partition by user_id order by start_month) month) as dt
from (
select
user_id,
date_add(start_date, interval - day(start_date) + 1 day) start_month
from (
select
r.user_id,
date(r.start_time) start_date
from hello_bike_riding_rcd r
left join m m1
on r.start_loc = m1.loc_nm
left join m m2
on r.end_loc = m2.loc_nm
where r.start_time between '2020-01-01' and '2025-02-28'
and concat(ifnull(m1.loc_type, ''), ifnull(m2.loc_type, '')) in ('写字楼地铁站', '地铁站写字楼')
group by
r.user_id,
date(r.start_time)
) t2
group by
user_id,
date_add(start_date, interval - day(start_date) + 1 day)
having count(*) >= 5
) t4
) t5
group by user_id, dt
having count(*) >= 3
)
select distinct
r.user_id,
if(t7.user_id is null, 0, 1) active_tag
from (
select distinct user_id
from hello_bike_riding_rcd
) r
left join t7
using(user_id);
|
| 2024-12-30 | Halo出行-通勤活跃用户标签开发 |
with m as (
select *
from (
select
loc_nm,
case
when loc_nm like '%研究所'
then '写字楼'
when loc_nm like '%西'
then '地铁站'
else loc_type
end as loc_type
from gd_loc_map
group by
loc_nm,
loc_type
) t
where loc_type not in ('写字楼', '地铁站')
)
,t7 as (
select distinct user_id
from (
select
user_id,
date_sub(start_month, interval dense_rank()over(partition by user_id order by start_month) month) as dt
from (
select
user_id,
date_add(start_date, interval - day(start_date) + 1 day) start_month
from (
select
r.user_id,
date(r.start_time) start_date
from hello_bike_riding_rcd r
left join m m1
on r.start_loc = m1.loc_nm
left join m m2
on r.end_loc = m2.loc_nm
where r.start_time between '2020-01-01' and '2025-02-28'
and concat(ifnull(m1.loc_type, ''), ifnull(m2.loc_type, '')) in ('写字楼地铁站', '地铁站写字楼')
group by
r.user_id,
date(r.start_time)
) t2
group by
user_id,
date_add(start_date, interval - day(start_date) + 1 day)
having count(*) >= 5
) t4
) t5
group by user_id, dt
having count(*) >= 3
)
select distinct
r.user_id,
if(t7.user_id is null, 0, 1) active_tag
from (
select distinct user_id
from hello_bike_riding_rcd
) r
left join t7
using(user_id);
|
| 2024-12-30 | Halo出行-通勤活跃用户标签开发 |
with m as (
select
loc_nm,
case
when loc_nm like '%研究所'
then '写字楼'
when loc_nm like '%西'
then '地铁站'
else loc_type
end as loc_type
from gd_loc_map
where loc_type not in ('写字楼', '地铁站')
group by
loc_nm,
loc_type
)
,t7 as (
select distinct user_id
from (
select
user_id,
date_sub(start_month, interval dense_rank()over(partition by user_id order by start_month) month) as dt
from (
select
user_id,
date_add(start_date, interval - day(start_date) + 1 day) start_month
from (
select
r.user_id,
date(r.start_time) start_date
from hello_bike_riding_rcd r
left join m m1
on r.start_loc = m1.loc_nm
left join m m2
on r.end_loc = m2.loc_nm
where r.start_time between '2020-01-01' and '2025-02-28'
and concat(ifnull(m1.loc_type, ''), ifnull(m2.loc_type, '')) in ('写字楼地铁站', '地铁站写字楼')
group by
r.user_id,
date(r.start_time)
) t2
group by
user_id,
date_add(start_date, interval - day(start_date) + 1 day)
having count(*) >= 5
) t4
) t5
group by user_id, dt
having count(*) >= 3
)
select distinct
r.user_id,
if(t7.user_id is null, 0, 1) active_tag
from (
select distinct user_id
from hello_bike_riding_rcd
) r
left join t7
using(user_id);
|