gistfile1.txt

{-# LANGUAGE TemplateHaskell, MultiParamTypeClasses, FlexibleInstances, OverloadedStrings #-}

import Data.Int (Int64)
import Database.HDBC.PostgreSQL (connectPostgreSQL)
import Database.HDBC.Schema.PostgreSQL (driverPostgreSQL)
import Database.HDBC.Query.TH (defineTableFromDB)
import Database.Relational.Query

$(defineTableFromDB
  (connectPostgreSQL "dbname=testdb")
  driverPostgreSQL
  "PUBLIC" "todesking" [])

boolAnd :: Projection Flat (Maybe Bool) -> Projection OverWindow (Maybe Bool)
boolAnd =  unsafeAggregateOp "BOOL_AND"

countSequence :: Relation () Int64
countSequence =  aggregateRelation $ do
  wF <- query . relation
        $ do
          r <- query todesking
          return $ r >< boolAnd (r ! status' .=. value (Just 0)) `over` do asc $ r ! createdAt'
  return $ count (caseSearch [(wF ! snd', value $ Just (1 :: Int64))] unsafeValueNull)

{-
SELECT ALL COUNT (CASE WHEN T1.f2 THEN 1 ELSE NULL END) AS f0
 FROM (SELECT ALL T0.created_at AS f0, T0.status AS f1,
                  BOOL_AND ((T0.status = 0)) OVER ( ORDER BY T0.created_at ASC) AS f2
       FROM PUBLIC.todesking T0) T1
 -}