Experienced users and newcomers

Fork of Experienced users and newcomers by Richard Sekerak (WMCZ)
This query is marked as a draft This query has been published by Richard Sekerak (WMCZ).
SQL

SET @minEdits = 0; -- 1) number of edits the user has to have to be considered experienced
SET @minTenureDays = 365; -- 2) if the user registered @minTenureDays days prior to the event (@eventDate), they're experienced
SET @eventDate = '20230101000000'; -- 3) see @minTenureDays

SELECT
	user_name,
    user_editcount,
    IF(user_registration IS NOT NULL, user_registration, 'Unknown') AS user_registration,
    IF(
      (
        -- have at least @minEdits edits
        user_editcount >= @minEdits AND (
          -- user_registration is not populated for users who registered pre-2005
          user_registration IS NULL OR
          -- are registered for at least @minTenureDays
          DATEDIFF(@eventDate, user_registration) >= @minTenureDays
        )
      ),
      "Experienced",
      "Newcomer"
    ) AS is_newcomer
FROM user
WHERE user_name IN (
'akimek',
'AnetaHr',
'Anny Famos',
'CzechWalley',
'Daniela Bártová',
'Dominik Walkerr',
'Jakub Šlégr',
'Jakub Vyskočil',
'Jankrikava',
'Jay11Cz',
'Julineckapusinecka',
'Kamcasedl',
'Klararecmanova',
'KTadeáš',
'Lelliana',
'Marek Metyš',
'Martina.Kovarova',
'Nehlad',
'NomadCzech',
'Pavel Pavel 01',
'Petrlukac',
'Podzemnik',
'RedledHexafunk',
'Rklusak',
'Svajcr',
'Terka Pavlů',
'Tomashrabec',
'Veverous',
'Vojtěch Kovařík',
'ZůzaFotí',
'Bratříček047',
'1mich1',
'Acanthoplus discoidalis',
'AIID',
'Aquafoto',
'Balousekk',
'David Kennedy',
'Dirillo',
'Eliska Jindriskova',
'Elizabethintina',
'Eva Sedláčková',
'Flukes69',
'FotoZVYSOKA cz',
'Galerie magdalena',
'Gorgosek',
'Hanina77',
'Henta',
'Hirundo888',
'ChoseLoos',
'IKOSH15',
'ImNotRezy',
'Irena Gregová',
'Iva Jelínková',
'Ivan Ivanek',
'Jablíček1023',
'JanaJin',
'Jirkapitrys',
'Johnová Radka',
'Josef Silovský',
'Julie Hansonová',
'Karelohnislav',
'Kateřina Fárková',
'Kesamot11',
'Krauz Daniel',
'Kristýna SW',
'Kristyna.alex.t',
'Lasy',
'Lubor Jirsák',
'LuceBeebiBear',
'LuciCucova',
'Lucie Pelcová 014',
'Luiseach94',
'Magilego',
'Marcela.strelcova',
'Marfy Poláček',
'Martin Srb',
'Medowy',
'Megas.Lakkos',
'Michaela.mil',
'Mojmir Churavy',
'NatalieHeikenwälder',
'Nefronus',
'OJJ',
'Outdoormaniak',
'Pagacmartin',
'PetrMík1234',
'Petulka soukupova',
'Sarah.Roza.Betty',
'SelfishCZ',
'Semipepino',
'Sikora74',
'Spálenský David',
'Stepan.hasek',
'Stepanka Pauzova',
'Šantisvámin',
'Tadeáš Zaza',
'TheGoldTiger',
'TheKerouk',
'TomasNovotny29',
'Veronika Chrastilová',
'Vladimír Buldra',
'Vypust',
'Woodcraft.photo',
'Ylzerb',
'Zbyněk Fojtík',
'Zita Králová',
'ZL954',
'Albert Horáček',
'Czeva',
'Rosta.miksik',
'Dalibor Jan',
'Elitimm1',
'Littleomig',
'Lucie Bartonova',
'M.Rejha',
'MarketaVltavska',
'Matěj Malík (OPN)',
'Mirek256',
'Sasankoss',
'T.Bednarz',
'ZdendaPraha',
'Bohaatstvo',
'Emve76',
'Hlozanek',
'Jaroslav Polacek',
'Kptlarsen',
'Luciferinla666',
'Sagiserp',
'Tatranka66',
'TFerenczy',
'Vlaky a RV',
'Vojtasafr',
'WTM',
'Zelinka Marek',
'Carrion123',
'ChJn',
'Dobroš',
'Eliška Zeisková',
'František Šťastný',
'GeXeS',
'Infochodur',
'Jakub Hrouda',
'Jendasss',
'Jiri Zeman',
'JiriMatejicek',
'Kardoslavik',
'Kateřina Fleknová',
'KPX8',
'Lilly10 11',
'Petr Vodička',
'PetraJel',
'RadekOpl',
'Roman Vyšanský',
'Suisant7',
'The photonaut',
'Trachta',
'Vlach Pavel',
'Zdenek952',
'Zorka Sojka',
'Zuzana Zonova',
'Jan.Braborec',
'TRom12',
'Opaq Nitsu',
'Polda18',
'Petra Bumberová',
'Zdenkavicikova',
'RChvojka314',
'Petruška11',
'Rogalo1',
'Tobi 0052',
'Autumn with Tea',
'Sedma velmoc',
'Jiří Komárek',
'Petr Podroužek',
'Luděk007',
'Nadkachna',
'Trendolina',
'Matybezdeka',
'J.Broukal',
'Anaj7',
'Jana koub',
'Jožka.Rihak',
'MilKlep',
'Zoomtraveller',
'Petr Gogan Dvorak',
'Ivkej',
'Lubor Ferenc',
'Johny136',
'Magdalena.Jansova',
'Tojakofakt',
'Honza245',
'Věrka Há Horská',
'Jakob82',
'Pavbe',
'Stana Oves',
'Lenka Junková',
'Kacka33',
'DDMart',
'Podobenka by Petra',
'Dan.dol',
'GabrielaZelenkova',
'MajJech',
'Vesely.Robin',
'Viklal',
'Ing. Lukáš Zeman',
'Miroslav Frais',
'Mars123',
'LksNjmn',
'Nojono159',
'Alena Turková',
'Jan.urbanek85',
'Petrekfoto',
'Hozakzbynek',
'BrujaFea',
'Schoro29',
'Klára Hášová',
'Pendolino53',
'Zuzankaobr',
'Jst72',
'Davidddddddddddddd',
'Rybnikárová Adriana',
'Lucie Ltm',
'Kopernik30',
'Sedlo1234',
'Fionek',
'Nikolesa',
'Vladi1978',
'Honzis1999',
'RMity',
'Jiri Blaha Prague',
'Aktron',
'Berda01',
'Pohled 111',
'Luciefoto',
'Michal Daneš',
'Terii79',
'Ondřej Brýdl',
'Остап-Юліан Атерваль (Захарко)',
'IamMartinHermann',
'Karlíkovo foto',
'Zdenek Krchak',
'Prunelle92',
'Paveltencik',
'Gumruch',
'TOP Ten 2022',
'Lukas.stas',
'Verunecka007',
'Novotnymiro',
'Jakub Kapaník',
'Lukáš Krátký',
'Mária Cibová',
'Pánzhor',
'Janbery',
'Prokop Racek',
'Richard Sekerák (WMCZ)',
'David Jeřábek',
'Hejkalek',
'Jana Okrouhlá',
'Jaroslav Povolný',
'Pavel21kriz',
'Speaker44',
'Tucal 123',
'Rakushaaan',
'Vladkazi1',
'David18803',
'Hedvikova',
'Vape toms',
'Editej',
'Fanda713',
'Karel Kutheil',
'Martin Šlais',
'Ivo Rolčík',
'Sarbinka',
'Yrkadsgn1983',
'Michalkralfoto',
'Tynkaslukova',
'Edujar89',
'DavidQuiet',
'Dominikksliva',
'Davidzar',
'Szkandera.ondrej',
'RomanaP18',
'Fric.matej',
'NinoJirásek',
'Foticikralicek',
'Veronika.Mrstna',
'Alena Harbichová',
'Meschua',
'NatálieSaw',
'Petra Písařovicová',
'Susal9184',
'Bazi',
'Vasekvlks',
'ZSsen',
'Topi Pigula',
'Veliensis'
)
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
Submit Query Stop Query
All SQL code is licensed under CC0 License.
SQL

Checking query status...
