title: Translating query with lag function to sqlalchemy with flask?
body: <p>I'm trying to translate the following query to sqlAlchemy and can't seem to figure it out (I'm not even far yet):</p>
  <pre><code>    &quot;SELECT time, version_id FROM ( \
      SELECT \
      time, \
      version_id, \
      LAG(software_version_id) OVER (ORDER BY time) as previous_version_id \
      FROM device_checkins WHERE device_id = 001 AND time BETWEEN '2020-01-01' AND '2021-04-01') tt \
      WHERE previous_version_id IS NULL OR version_id != previous_version_id;&quot;
  </code></pre>
  <p>As far as I could figure out, I need the <code>select</code> function that sqlAlchemy provides but I'm running into trouble.</p>
  <p>Of course, in the python representation, we have a <code>DeviceCheckin</code> model with all the fields that are used here. I'd love all the help you might be able to provide.</p>
  <pre><code>class DeviceCheckin(ModelBase):
      __tablename__ = &quot;device_checkins&quot;
      time = db.Column(DateTimeUtc(), nullable=False)
      device_id = db.Column(sa.BigInteger, nullable=False)
      device = db.relationship(...)
  
      software_version_id = db.Column(...)
      software_version = db.relationship(...)
  </code></pre>