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 = '20210101000000'; -- 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 (
'1mich1',
'Acanthoplus discoidalis',
'Akimek',
'Albert Horáček',
'AnetaHr',
'Anny Famos',
'Aquafoto',
'Bohaatstvo',
'Bratříček047',
'CzechWalley',
'Czeva',
'Dalibor Jan',
'Daniela Bártová',
'David Kennedy',
'Dirillo',
'Dobroš',
'Dominik Walkerr',
'Eliska Jindriskova',
'Elitimm1',
'Elizabethintina',
'Emve76',
'Eva Sedláčková',
'Flukes69',
'FotoZVYSOKA cz',
'GabrielaZelenkova',
'Galerie magdalena',
'GrandEscogriffe',
'Hanina77',
'Hasomuj',
'Hirundo888',
'Hlozanek',
'ChoseLoos',
'IKOSH15',
'ImNotRezy',
'Irena Gregová',
'Iva Jelínková',
'Ivan Ivanek',
'Jablíček1023',
'Jakub Šlégr',
'Jakub Vyskočil',
'JanaJin',
'Jankrikava',
'Jaroslav Polacek',
'Jay11Cz',
'Jirkapitrys',
'Johnová Radka',
'Josef Silovský',
'Julineckapusinecka',
'Kamcasedl',
'Kateřina Fárková',
'Kesamot11',
'Klararecmanova',
'Kptlarsen',
'Krauz Daniel',
'Kristýna SW',
'Kristyna.alex.t',
'KTadeáš',
'Lasy',
'Lelliana',
'Littleomig',
'Lubor Ferenc',
'Lubor Jirsák',
'LuceBeebiBear',
'LuciCucova',
'Lucie Bartonova',
'Lucie Pelcová 014',
'Luciferinla666',
'Luiseach94',
'M.Rejha',
'Magilego',
'Marcela.strelcova',
'Marek Metyš',
'Marfy Poláček',
'Mária Cibová',
'MarketaVltavska',
'Martin Srb',
'Martina.Kovarova',
'Matěj Malík (OPN)',
'Medowy',
'Megas.Lakkos',
'Michaela.mil',
'Michal Josef Špaček (WMCZ)',
'Mirek256',
'Mojmir Churavy',
'NatalieHeikenwälder',
'Nefronus',
'Nehlad',
'NomadCzech',
'OJJ',
'Outdoormaniak',
'Pavel Pavel 01',
'Petrlukac',
'PetrMík1234',
'Petulka soukupova',
'Plánovací kalendář',
'RedledHexafunk',
'Rklusak',
'Sagiserp',
'Sasankoss',
'Semipepino',
'SchiDD',
'Sikora74',
'Spálenský David',
'Stepan.hasek',
'Stepanka Pauzova',
'Svajcr',
'Tadeáš Zaza',
'Tatranka66',
'Terka Pavlů',
'TFerenczy',
'TheGoldTiger',
'Tomashrabec',
'TomasNovotny29',
'Veronika Chrastilová',
'Veverous',
'Vlaky a RV',
'Vojtasafr',
'Vojtěch Kovařík',
'Vypust',
'Woodcraft.photo',
'WTM',
'Ylzerb',
'Zbyněk Fojtík',
'ZdendaPraha',
'Zelinka Marek',
'Zita Králová',
'ZůzaFotí'
)

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...