1

I've been working on a query to count the number of days a user's posts have had votes up, with no votes down on any day. But it seems like it's missing some posts. Users that have Four Ayes, do not seem to show up in this report.

Can someone tell me where I'm going wrong with this query?

WITH FourAyes (Id, DisplayName, Title, Up, Down, Created)
AS
(
    SELECT p.Id, u.DisplayName, p.Title,
       SUM(CASE WHEN v.VoteTypeId = 2 THEN 1 ELSE 0 END),
       SUM(CASE WHEN v.VoteTypeId = 3 THEN 1 ELSE 0 END),
       CONVERT(DATE, p.CreationDate)
    FROM Posts p
       JOIN Users u on u.Id = OwnerUserId
       JOIN Votes v on p.Id = v.PostId and v.VoteTypeId between 2 and 3
    WHERE u.DisplayName = '##Name##'
      AND p.CreationDate > '2022-12-14 21:00'
    GROUP BY p.id, u.DisplayName, p.Title, p.CreationDate
)
SELECT Id as [Post Link], DisplayName, Title, Up, Created
FROM FourAyes
WHERE Down = 0
ORDER BY Id
2
  • 3
    SEDE is updated once a week, on Sunday. That could explain why you see users now with the hat, while SEDE still has to catch-up. The query looks fine.
    – rene
    Commented Dec 31, 2022 at 6:40
  • 1
    @rene this query still doesn’t work. So it’s not sede update times. I was trying to count the days with >0 upvotes not the number of votes. I used convert to strip time off the dates. Commented Jan 2, 2023 at 12:05

1 Answer 1

4

SEDE isn't accurate enough I guess, due to its weekly scheduled refresh on Sunday. Instead you could leverage the Stack Exchange API. Specially the users/posts endpoint with an appropriate filter. I've tried to mimic your query in the following stack snippet. Your userid and the site that we query are hard coded in the script. I leave it as an exercise for the reader to adapt and enhance the features when warranted.

var userid = 1244491;
var site = 'meta';
var host = 'https://api.stackexchange.com';
var url = `${host}/2.3/users/${userid}/posts?fromdate=1670976000&order=desc&sort=activity&site=${site}&filter=!)qxdRwDaP7)QKzhUN*sI`

function createLink(item) {
  var td = document.createElement('td');
  var a = document.createElement('a');
  a.href = item.link;
  a.textContent = item.link
  td.appendChild(a);
  return td;
}

function createUpvote(item) {
  var td = document.createElement('td');
  td.textContent = item.up_vote_count;
  return td;
}

function createDate(item) {
  var td = document.createElement('td');
  var date = new Date(item.creation_date *1000);
  td.textContent = date.getFullYear()+'-'+(date.getMonth()+1)+'-' + date.getDate()
  return td;
}

function createCard(item) {
  var tr = document.createElement('tr');
 
  tr.appendChild(createLink(item));
  tr.appendChild(createUpvote(item));
  tr.appendChild(createDate(item));
  
  return tr;
}

function append(elements) {
  var result = document.getElementById('result');
  for(var element of elements) {
     result.appendChild(element);
  }
}

fetch(url)
  .then(resp => resp.json())
  .then(json => json.items.filter( i => i.down_vote_count === 0))
  .then(items => items.map(i => createCard(i)))
  .then(elements => append(elements));
<table>
  <thead>
    <th>link</th>
    <th>upvotes</th>
    <th>date</th>
  </thead>
  <tbody id='result'>
  </tbody>
</table>

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .