排名

用户解题统计

过去一年提交了

勋章 ①金银铜：在竞赛中获得第一二三名；②好习惯：自然月10天提交；③里程碑：解决1/2/5/10/20/50/100/200题；④每周打卡挑战：完成每周5题，每年1月1日清零。

收藏

收藏日期 题目名称 解决状态
没有收藏的题目。

评论笔记

评论日期 题目名称 评论内容 站长评论
2024-12-30 Halo出行-通勤活跃用户标签开发  
24年10、11、12这三个月连续啊
user_id	dt	start_loc	loc_type	end_loc	loc_type_1
u000911	2024-09-24	恒通国际商务园	写字楼	望京	地铁站
u000911	2024-09-26	阜通	地铁站	凯德广场	写字楼
u000911	2024-10-05	望京南	地铁站	瀚海国际大厦	写字楼
u000911	2024-10-07	望京南	地铁站	西门子大厦	写字楼
u000911	2024-10-08	中关村电子城	写字楼	望京	地铁站
u000911	2024-10-09	北京机床研究所	地铁站	瀚海国际大厦	写字楼
u000911	2024-10-10	瀚海国际大厦	写字楼	望京	地铁站
u000911	2024-10-11	北京机床研究所	地铁站	恒通国际商务园	写字楼
u000911	2024-10-15	望京	地铁站	瀚海国际大厦	写字楼
u000911	2024-10-18	望京	地铁站	将台西	写字楼
u000911	2024-10-18	望京	地铁站	恒通国际商务园	写字楼
u000911	2024-10-22	将台西	写字楼	阜通	地铁站
u000911	2024-10-22	北京机床研究所	地铁站	西门子大厦	写字楼
u000911	2024-10-23	北京机床研究所	地铁站	西门子大厦	写字楼
u000911	2024-10-23	西门子大厦	写字楼	望京	地铁站
u000911	2024-10-24	将台西	写字楼	望京南	地铁站
u000911	2024-10-25	阜通	地铁站	瀚海国际大厦	写字楼
u000911	2024-11-01	凯德广场	写字楼	望京南	地铁站
u000911	2024-11-07	西门子大厦	写字楼	望京	地铁站
u000911	2024-11-11	望京南	地铁站	将台西	写字楼
u000911	2024-11-16	望京	地铁站	天启大厦	写字楼
u000911	2024-11-23	望京	地铁站	将台西	写字楼
u000911	2024-12-03	中关村电子城	写字楼	望京南	地铁站
u000911	2024-12-04	北京机床研究所	地铁站	西门子大厦	写字楼
u000911	2024-12-05	阜通	地铁站	将台西	写字楼
u000911	2024-12-08	天启大厦	写字楼	阜通	地铁站
u000911	2024-12-13	将台西	写字楼	北京机床研究所	地铁站
u000911	2024-12-14	阜通	地铁站	凯德广场	写字楼
u000911	2024-12-17	望京南	地铁站	西门子大厦	写字楼
u000911	2024-12-18	凯德广场	写字楼	阜通	地铁站
 
北京机床研究所你确定是地铁站？题干不是提示你了么。“高德地图接口部分分类数据有误。”
2024-12-30 Halo出行-通勤活跃用户标签开发  
从高德接口获取，近期已有业务同事反映部分地点分类不准的情况 地点分类不准是需要sql处理吗？
 
对，手动核对准不准。

提交记录

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