# Optimizing sqlite aggregation functions for low end android devices

**URL:** <https://forum.ionicframework.com/t/optimizing-sqlite-aggregation-functions-for-low-end-android-devices/220619>\
**Category:** cordova\
**Created:** [February 18, 2022, 4:23pm UTC](https://forum.ionicframework.com/t/optimizing-sqlite-aggregation-functions-for-low-end-android-devices/220619 "2022-02-18T16:23:20Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![CodeWithOz](https://avatars.discourse-cdn.com/v4/letter/c/e9bcb4/32.png) [@CodeWithOz](https://forum.ionicframework.com/u/CodeWithOz)\
**Post date:** [February 18, 2022, 4:23pm UTC](https://forum.ionicframework.com/t/optimizing-sqlite-aggregation-functions-for-low-end-android-devices/220619/1 "2022-02-18T16:23:20Z")

</div>

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:

```auto
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](https://stackoverflow.com/questions/71127875/sqlite-count-the-number-of-consecutive-matching-rows-while-excluding-them). Any ideas for how I can optimize this query further to make it more performant on low devices? Thanks!