SEDE query to find progress on Four Ayes hat?

Question

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

Answer 1

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>