I have a sqlite operation that stalls and then crashes my cordova app when run on a table with hundreds of rows. This is the query:
WITH prev_types AS ( SELECT t1.*, t1.type <> COALESCE(t2.type, '') flag, MAX(t2.date) max_time FROM chatMessages t1 LEFT JOIN chatMessages t2 ON t2.date < t1.date GROUP BY t1.id ), sum_flags AS ( SELECT pt1.*, (SELECT SUM(pt2.flag) FROM prev_types pt2 WHERE pt2.date <= pt1.date) grp FROM prev_types pt1 ), cte AS ( SELECT sf1.*, (SELECT COUNT(*) FROM sum_flags sf2 WHERE sf2.grp = sf1.grp) count, (SELECT COUNT(*) FROM sum_flags sf2 WHERE sf2.grp = sf1.grp AND sf2.date <= sf1.date) rn FROM sum_flags sf1 ) SELECT id, type, date, message, CASE WHEN type = 'groupedMsgs' THEN count END numConsecutiveItems FROM cte WHERE numConsecutiveItems IS NULL OR rn = 1 ORDER BY date;
It’s intended to get a count of consecutive rows of a particular type. The full context of this can be read here. Any ideas for how I can optimize this query further to make it more performant on low devices? Thanks!