Sep19 - Comments + Author names - 1043 - Stack Exchange Data Explorer

Sep19 - Comments + Author names - 1043

Please login or register to vote for this query.

(click on this box to dismiss)
Q&A for people who build and repair bicycles, people who train cycling, or commute on bicycles
WITH CommentCounts AS (
    SELECT 
        c.UserId AS CommentorId, 
        COUNT(DISTINCT c.Id) AS CommentCount
    FROM Comments c
    WHERE c.PostId IN (34784842, 20795628, 39003201, 49687760, 13478143, 3433300, 25661346, 
               54703119, 3331372, 27574013, 2068371, 12448660, 14528940, 30364967, 
               45374162, 17335326, 5155064, 26201470, 33258221, 52819365, 63023147, 
               42503710, 15747107, 30635615, 28787850, 24869361, 67629882, 22616491, 
               9311585, 17284149, 17510047, 17817649, 25368121, 25957456, 28701875, 
               33854963, 42051696, 42755494, 44996565, 53608740, 49445791, 58347470, 
               68739091, 73074030, 76605477, 36184192, 43366543, 43876351, 70668065, 
               14186333, 18977525, 21897585, 34769186, 35693203, 23854239, 40001851, 
               64186678, 19121603, 14117002, 47537787, 51673759, 30653545, 3614515, 
               32713411, 42365034, 2083166, 27491718, 22640949, 24393769, 21550325, 
               19507833, 412499, 16326307, 45761277, 7286526, 6754493, 8212695, 12699024, 
               13331877, 14321759, 14340606, 14961023, 15930869, 16732178, 18385550, 
               19689342, 20855937, 20968486, 21003302, 21023646, 21364896, 23065379, 
               23611142, 32590628, 35330568, 27483789, 27585761, 35994545,31048643, 29923606, 30168492, 30838892, 31904843, 32833713, 33209622, 
               33806349, 34080092, 34119555, 36207322, 36425499, 36447355, 36527467, 
               40415270, 36750907, 68128473, 77039712, 38408320, 38743012, 42942297, 
               39774276, 40936057, 41145703, 41896757, 53256846, 43428542, 49517072, 
               49458790, 50660316, 46015117, 47000575, 47189206, 49532903, 51252275, 
               53226534, 69203845, 55380438, 55687618, 57613886, 59291605, 58569855, 
               59271865, 59178853, 59365568, 60226397, 61102052, 63214860, 63284750, 
               63961092, 65610402, 65716045, 67580957, 74358704, 72952108, 73792461, 
               73863818, 74345861, 76113937, 77221732, 673935, 3033401, 6548803, 
               3797025, 4979000, 8974989, 8361320, 10680072, 11305280, 12256808, 
               12947024, 13287750, 15211164, 15731643, 16408256, 16530239, 17219488, 
               17180002, 49228899, 19313781, 20175612, 21263153, 26369450, 28370918, 
               29397343, 30449688, 30767493, 32521988, 32926993, 35051694, 35546836, 
               35693040, 36071586, 36575536, 37401128,40710930, 42395303, 44361018, 44417624, 44736690, 45523953, 46132294, 
               47536513, 47556367, 58068828, 49981109, 50085013, 50507296, 52535154, 
               54330350, 55633576, 56224370, 57270828, 57885967, 59625448, 60026544, 
               62644866, 63811627, 69260879, 68357612, 69652231, 70234572, 74108656, 
               74614963, 9756123, 13380190, 12842420, 13589148, 14569880, 15255130, 
               17118366, 19254755, 27206089, 27838490, 27985611, 38926934, 34350973, 
               34769720, 38812378, 36708264, 36997094, 37863046, 39248115, 39902888, 
               40790856, 40842800, 48385765, 43166848, 43349486, 46612037, 46774250, 
               48802886, 49904974, 57912282, 62462560, 63672573, 66959061, 71409194, 
               75194888, 8881910, 9970665, 12385125, 13968471, 20808092, 14169684, 
               14860395, 14983623, 14977250, 19135912, 19732032, 22384747, 26972241, 
               28374165, 31158524, 31261934, 32284685, 34097066, 40172994, 46512057, 
               46960999, 47799043, 48475233, 53253474, 62919851, 67847144, 69259093, 
               71285048, 7570264, 11221787, 9765739, 12157521, 12415238, 16133804, 
               46414532, 17240637, 18435933, 31288731, 26085062, 29620560, 29132359, 
               38727387, 36377708, 39619424, 43765323, 47283007, 48657597, 54352888, 
               57849995, 59298544, 60953148, 62686472, 64628045, 68811728, 3797990, 
               4368404, 6724251, 7653897,13278716, 21245486, 26348351, 29915873, 43911708, 37623376, 68036487, 
               71067222, 2938229, 9344539, 18283126, 17406651, 34819909, 41604675, 
               46264683, 5526663, 29006245, 22864585, 30350567, 30701958, 57456580, 
               42998243, 11321447, 29732425, 17843719, 19691307, 27708294, 35551997, 
               27647591, 12466879, 17915447, 54322282, 73902612, 9685644, 17219959, 
               18281626, 19812942, 22262516, 25826995, 51537172, 4709080, 25593406, 
               48407571, 30197080, 26167811, 32427941, 45377998, 30601863, 66412418, 
               31657828, 54573489, 46915020, 29828877, 8956066, 52887769, 9440492, 
               72197652, 21304639, 8932653, 1121859, 40951643, 14072367, 36366737, 
               45272171, 17349755, 32537719, 12698962, 64740722, 35877512, 11297534, 
               30249913, 30724888, 12467865, 15580411, 30358006, 18978874, 30367210, 
               40487704, 12788320, 8668741, 7513356, 38480188, 38780591, 39410811)  -- Limiting to the first two PostIds
    GROUP BY c.UserId
)
SELECT DISTINCT 
    c.UserId AS CommentorId, 
    u.DisplayName AS CommentorName, 
    u.Reputation AS CommentorReputation, 
    MIN(c.CreationDate) AS FirstCommentDate, 
    cc.CommentCount, 
    v.VoteDate, 
    v.VoteTypeId, 
    c.Id AS CommentId,             -- Comment ID
    c.Text AS CommentBody,         -- Comment Body
    c.CreationDate AS CommentDate  -- Comment Creation Date
FROM Comments c
LEFT JOIN CommentCounts cc ON c.UserId = cc.CommentorId
LEFT JOIN (
    SELECT 
        v.PostId, 
        MIN(v.CreationDate) AS VoteDate, 
        v.VoteTypeId
    FROM Votes v
    WHERE v.PostId IN (34784842, 20795628, 39003201, 49687760, 13478143, 3433300, 25661346, 
               54703119, 3331372, 27574013, 2068371, 12448660, 14528940, 30364967, 
               45374162, 17335326, 5155064, 26201470, 33258221, 52819365, 63023147, 
               42503710, 15747107, 30635615, 28787850, 24869361, 67629882, 22616491, 
               9311585, 17284149, 17510047, 17817649, 25368121, 25957456, 28701875, 
               33854963, 42051696, 42755494, 44996565, 53608740, 49445791, 58347470, 
               68739091, 73074030, 76605477, 36184192, 43366543, 43876351, 70668065, 
               14186333, 18977525, 21897585, 34769186, 35693203, 23854239, 40001851, 
               64186678, 19121603, 14117002, 47537787, 51673759, 30653545, 3614515, 
               32713411, 42365034, 2083166, 27491718, 22640949, 24393769, 21550325, 
               19507833, 412499, 16326307, 45761277, 7286526, 6754493, 8212695, 12699024, 
               13331877, 14321759, 14340606, 14961023, 15930869, 16732178, 18385550, 
               19689342, 20855937, 20968486, 21003302, 21023646, 21364896, 23065379, 
               23611142, 32590628, 35330568, 27483789, 27585761, 35994545,31048643, 29923606, 30168492, 30838892, 31904843, 32833713, 33209622, 
               33806349, 34080092, 34119555, 36207322, 36425499, 36447355, 36527467, 
               40415270, 36750907, 68128473, 77039712, 38408320, 38743012, 42942297, 
               39774276, 40936057, 41145703, 41896757, 53256846, 43428542, 49517072, 
               49458790, 50660316, 46015117, 47000575, 47189206, 49532903, 51252275, 
               53226534, 69203845, 55380438, 55687618, 57613886, 59291605, 58569855, 
               59271865, 59178853, 59365568, 60226397, 61102052, 63214860, 63284750, 
               63961092, 65610402, 65716045, 67580957, 74358704, 72952108, 73792461, 
               73863818, 74345861, 76113937, 77221732, 673935, 3033401, 6548803, 
               3797025, 4979000, 8974989, 8361320, 10680072, 11305280, 12256808, 
               12947024, 13287750, 15211164, 15731643, 16408256, 16530239, 17219488, 
               17180002, 49228899, 19313781, 20175612, 21263153, 26369450, 28370918, 
               29397343, 30449688, 30767493, 32521988, 32926993, 35051694, 35546836, 
               35693040, 36071586, 36575536, 37401128,40710930, 42395303, 44361018, 44417624, 44736690, 45523953, 46132294, 
               47536513, 47556367, 58068828, 49981109, 50085013, 50507296, 52535154, 
               54330350, 55633576, 56224370, 57270828, 57885967, 59625448, 60026544, 
               62644866, 63811627, 69260879, 68357612, 69652231, 70234572, 74108656, 
               74614963, 9756123, 13380190, 12842420, 13589148, 14569880, 15255130, 
               17118366, 19254755, 27206089, 27838490, 27985611, 38926934, 34350973, 
               34769720, 38812378, 36708264, 36997094, 37863046, 39248115, 39902888, 
               40790856, 40842800, 48385765, 43166848, 43349486, 46612037, 46774250, 
               48802886, 49904974, 57912282, 62462560, 63672573, 66959061, 71409194, 
               75194888, 8881910, 9970665, 12385125, 13968471, 20808092, 14169684, 
               14860395, 14983623, 14977250, 19135912, 19732032, 22384747, 26972241, 
               28374165, 31158524, 31261934, 32284685, 34097066, 40172994, 46512057, 
               46960999, 47799043, 48475233, 53253474, 62919851, 67847144, 69259093, 
               71285048, 7570264, 11221787, 9765739, 12157521, 12415238, 16133804, 
               46414532, 17240637, 18435933, 31288731, 26085062, 29620560, 29132359, 
               38727387, 36377708, 39619424, 43765323, 47283007, 48657597, 54352888, 
               57849995, 59298544, 60953148, 62686472, 64628045, 68811728, 3797990, 
               4368404, 6724251, 7653897,13278716, 21245486, 26348351, 29915873, 43911708, 37623376, 68036487, 
               71067222, 2938229, 9344539, 18283126, 17406651, 34819909, 41604675, 
               46264683, 5526663, 29006245, 22864585, 30350567, 30701958, 57456580, 
               42998243, 11321447, 29732425, 17843719, 19691307, 27708294, 35551997, 
               27647591, 12466879, 17915447, 54322282, 73902612, 9685644, 17219959, 
               18281626, 19812942, 22262516, 25826995, 51537172, 4709080, 25593406, 
               48407571, 30197080, 26167811, 32427941, 45377998, 30601863, 66412418, 
               31657828, 54573489, 46915020, 29828877, 8956066, 52887769, 9440492, 
               72197652, 21304639, 8932653, 1121859, 40951643, 14072367, 36366737, 
               45272171, 17349755, 32537719, 12698962, 64740722, 35877512, 11297534, 
               30249913, 30724888, 12467865, 15580411, 30358006, 18978874, 30367210, 
               40487704, 12788320, 8668741, 7513356, 38480188, 38780591, 39410811)
    AND v.VoteTypeId = 3  -- Downvotes on the answer
    GROUP BY v.PostId, v.VoteTypeId
) v ON c.PostId = v.PostId AND YEAR(c.CreationDate) = YEAR(v.VoteDate)
LEFT JOIN Users u ON c.UserId = u.Id
WHERE c.PostId IN (34784842, 20795628, 39003201, 49687760, 13478143, 3433300, 25661346, 
               54703119, 3331372, 27574013, 2068371, 12448660, 14528940, 30364967, 
               45374162, 17335326, 5155064, 26201470, 33258221, 52819365, 63023147, 
               42503710, 15747107, 30635615, 28787850, 24869361, 67629882, 22616491, 
               9311585, 17284149, 17510047, 17817649, 25368121, 25957456, 28701875, 
               33854963, 42051696, 42755494, 44996565, 53608740, 49445791, 58347470, 
               68739091, 73074030, 76605477, 36184192, 43366543, 43876351, 70668065, 
               14186333, 18977525, 21897585, 34769186, 35693203, 23854239, 40001851, 
               64186678, 19121603, 14117002, 47537787, 51673759, 30653545, 3614515, 
               32713411, 42365034, 2083166, 27491718, 22640949, 24393769, 21550325, 
               19507833, 412499, 16326307, 45761277, 7286526, 6754493, 8212695, 12699024, 
               13331877, 14321759, 14340606, 14961023, 15930869, 16732178, 18385550, 
               19689342, 20855937, 20968486, 21003302, 21023646, 21364896, 23065379, 
               23611142, 32590628, 35330568, 27483789, 27585761, 35994545,31048643, 29923606, 30168492, 30838892, 31904843, 32833713, 33209622, 
               33806349, 34080092, 34119555, 36207322, 36425499, 36447355, 36527467, 
               40415270, 36750907, 68128473, 77039712, 38408320, 38743012, 42942297, 
               39774276, 40936057, 41145703, 41896757, 53256846, 43428542, 49517072, 
               49458790, 50660316, 46015117, 47000575, 47189206, 49532903, 51252275, 
               53226534, 69203845, 55380438, 55687618, 57613886, 59291605, 58569855, 
               59271865, 59178853, 59365568, 60226397, 61102052, 63214860, 63284750, 
               63961092, 65610402, 65716045, 67580957, 74358704, 72952108, 73792461, 
               73863818, 74345861, 76113937, 77221732, 673935, 3033401, 6548803, 
               3797025, 4979000, 8974989, 8361320, 10680072, 11305280, 12256808, 
               12947024, 13287750, 15211164, 15731643, 16408256, 16530239, 17219488, 
               17180002, 49228899, 19313781, 20175612, 21263153, 26369450, 28370918, 
               29397343, 30449688, 30767493, 32521988, 32926993, 35051694, 35546836, 
               35693040, 36071586, 36575536, 37401128,40710930, 42395303, 44361018, 44417624, 44736690, 45523953, 46132294, 
               47536513, 47556367, 58068828, 49981109, 50085013, 50507296, 52535154, 
               54330350, 55633576, 56224370, 57270828, 57885967, 59625448, 60026544, 
               62644866, 63811627, 69260879, 68357612, 69652231, 70234572, 74108656, 
               74614963, 9756123, 13380190, 12842420, 13589148, 14569880, 15255130, 
               17118366, 19254755, 27206089, 27838490, 27985611, 38926934, 34350973, 
               34769720, 38812378, 36708264, 36997094, 37863046, 39248115, 39902888, 
               40790856, 40842800, 48385765, 43166848, 43349486, 46612037, 46774250, 
               48802886, 49904974, 57912282, 62462560, 63672573, 66959061, 71409194, 
               75194888, 8881910, 9970665, 12385125, 13968471, 20808092, 14169684, 
               14860395, 14983623, 14977250, 19135912, 19732032, 22384747, 26972241, 
               28374165, 31158524, 31261934, 32284685, 34097066, 40172994, 46512057, 
               46960999, 47799043, 48475233, 53253474, 62919851, 67847144, 69259093, 
               71285048, 7570264, 11221787, 9765739, 12157521, 12415238, 16133804, 
               46414532, 17240637, 18435933, 31288731, 26085062, 29620560, 29132359, 
               38727387, 36377708, 39619424, 43765323, 47283007, 48657597, 54352888, 
               57849995, 59298544, 60953148, 62686472, 64628045, 68811728, 3797990, 
               4368404, 6724251, 7653897,13278716, 21245486, 26348351, 29915873, 43911708, 37623376, 68036487, 
               71067222, 2938229, 9344539, 18283126, 17406651, 34819909, 41604675, 
               46264683, 5526663, 29006245, 22864585, 30350567, 30701958, 57456580, 
               42998243, 11321447, 29732425, 17843719, 19691307, 27708294, 35551997, 
               27647591, 12466879, 17915447, 54322282, 73902612, 9685644, 17219959, 
               18281626, 19812942, 22262516, 25826995, 51537172, 4709080, 25593406, 
               48407571, 30197080, 26167811, 32427941, 45377998, 30601863, 66412418, 
               31657828, 54573489, 46915020, 29828877, 8956066, 52887769, 9440492, 
               72197652, 21304639, 8932653, 1121859, 40951643, 14072367, 36366737, 
               45272171, 17349755, 32537719, 12698962, 64740722, 35877512, 11297534, 
               30249913, 30724888, 12467865, 15580411, 30358006, 18978874, 30367210, 
               40487704, 12788320, 8668741, 7513356, 38480188, 38780591, 39410811)
GROUP BY 
    c.UserId, 
    u.DisplayName, 
    u.Reputation, 
    v.VoteDate, 
    v.VoteTypeId, 
    cc.CommentCount, 
    c.Id,             -- Grouping by Comment ID
    c.Text,           -- Grouping by Comment Body
    c.CreationDate    -- Grouping by Comment Creation Date
edited sep 20
jon.doe34463
created sep 19
anonymous
Enter Parameters

Options: Text-only results Include execution plan
Switch to meta site
Hold tight while we fetch your results
Results[:current/:total] Messages Graph Execution Plan
Download CSV Download XML
:records returned in :time ms:cached