Title
#users-public
g

Grant Birkinbine

09/29/2022, 12:36 AM
About 💡 I am trying to write time series events about when a user leaves a star on a GitHub repo All my data is being collected and stored in questdb okay. All I am trying to do is write a query that looks for all events in the database that matches a
repo_name
and counts them all up to see how many stars it got during X timeframe create statement
CREATE TABLE IF NOT EXISTS
  stars(repo_name string,
  created_at TIMESTAMP
  )
  TIMESTAMP(created_at)
PARTITION BY HOUR;
where
repo_name
is the name of the GitHub repository and
created_at
is the time that a user clicked the "star" button in GitHub and "created" a "star event"
example curl to insert a record
curl -k -G --data-urlencode "query=INSERT INTO stars VALUES('<github_user>/<github_repo>', '2022-09-11 07:17:05')" <https://localhost:443/exec>
checking records Running a
SELECT * FROM 'stars';
shows that all the records are there as they should be using my small sample set (screenshot below) Counting all the events with
SELECT count() FROM 'stars';
looks good too (screenshot below) the issue However... when I try to run an actual query to get a count of all star events, the records are just so bamboozled its unusable. I'm not sure what I am doing wrong but the first value that gets entered has way more events returned than it should.
SELECT repo_name, COUNT(*) AS count FROM 'stars' WHERE created_at > dateadd('d', -1, now()) GROUP BY repo_name ORDER BY count DESC LIMIT 20;
The
nfl/react-helmet
repo should only have
1
as its count as there is only one record in my entire table with
nfl/react-helmet
as the
repo_name
(meaning in my sample dataset it has only received one GitHub star). The numbers get less jumbled as you go down the list of results (in screenshot below) but I am so confused. 📖 TL;DR: All I want is a way to query all the records in my table and get totals for how many names an identical
repo_name
string occurs within a given timeslice
Andrey Pechkurov

Andrey Pechkurov

09/29/2022, 6:06 AM
Hello, Could you run the following query and share the output?
SELECT * FROM 'stars' WHERE repo_name = 'nfl/react-helmet';
5:20 AM
Thanks. This looks a lot like a bug. Is it possible to share your database table with us? Just compress the
stars
directory under
<root_dir>/db