My goal I'm trying to track and display a user's daily streak posting to my app, but struggling to write a query that works reliably and returns an accurate count. Some context My app has a prompt and a post…
I have a MongoDB collection that stores some test submissions. The highlevel schema looks as follows. { "lessonType": "quiz", "userId": "639cb6d9513a420026eddca1" "courseId": "639cb6d9513a420026eddca2", "lessonId": "639cb6d9513a420026eddca3", ...some_more_fields } Now there are two indexes on this collection. first one: { courseId: 1,…
I have the following postgres query that's meant to update all message entries: update message m set user_id = u.id from "user" u where u.user_pgid = m.user_pgid The trouble is that I have ~300,000,000 message records, and postgres seems to…
I have a query like this: EXPLAIN SELECT b.* FROM bills b WHERE (b.id IN (SELECT u.id FROM bills u WHERE u.updated BETWEEN '2023-12-19 14:17:48' AND '2023-12-19 14:27:40' AND u.organisation_id = 'ABC123') OR b.id IN (SELECT c.id FROM bills c…
I'm writing a query in MySQL for a Grafana dashboard, but the performance of only this particular query is very poor. This is the query: SELECT br.reading ,CONCAT(ino.project,"_",SUBSTRING_INDEX(br.sensor,"_",-1)) AS new_metric ,ino.vessel ,ADDTIME(DATE('2023-01-01'),-TIMEDIFF(ino.date,br.datetime)) AS norm_date ,br.sensor FROM db.milestones br INNER JOIN…
I have a three table. When I execute below query it takes 3 to 5 min. SELECT u.Id,cmc.completionstate,cmc.coursemoduleid FROM User u JOIN role_assignments ra ON u.id =ra.userid LEFT JOIN course_modules_completion cmc ON u.id=cmc.userid LIMIT 1000000000 Execution Plan: id select_type table…
I am currently working on a project where a wordcloud is created for the NYT. Therefore I am scraping all the articles from the NYT from their API, and then store dates of the article, the articles href and all…
I have a SQL query which works but is slow. I wondered if there is a more efficient way to express it using joins. Scenario: Tables: productions, scripts and a join table, productions_scripts productions id (int) production (text) scripts id…
I have following timeseries data table, Id Object_Id Datetime Sensor1 Sensor2 1 175 '2022-03-24 22:01:00' 0 0 2 175 '2022-03-24 22:02:00' 0 0 3 175 '2022-03-24 22:03:00' 5.5699381666667 81.342836833333 4 175 '2022-03-24 22:04:00' 5.5668366666667 81.281143 ... ... ... ... ...…
I have two tables T1 and T2. I'm doing a simple inner join select t1.a, t1.b, t1.c from T1 t1 inner join T2 t2 on t1.c = t2.c Table T1 has got 2 million rows and T2 has got 4…