Converting SQL query into KnexJS

Question

I am trying to convert SQL query into KnexJS format, but current KnexJS query gives me the following error.

• syntax error at or near "as" stack: error: syntax error at or near "as"

Here are the original query and the one I've been working on for the KnexJS. Please correct my KnexJS query.

Thank you in advance!

Original SQL query:

select count(distinct date) 
from task_history
where 
store_id = 100 and date >
(select date from (
select date, count(*) as count_all, count(case when finish_time is not null 
then 1 else null end) as count_finished
from task_history
where store_id = 100 
group by date
order by count_finished, date desc
fetch first row only) as get_max_date)

KnexJS query:

.table("task_history")
.count(db.raw("'distinct date'"))
.where('store_id', 100)
.where('date', '>', function() {
    this.select('date')
    .from(function() {
        this.select('date')
        .table("task_history")
        .first()
        .count('* as count_all')
        .count(db.raw(`case when finish_time is not null then 1 else null end as count_finished`))
        .where('store_id', 100)
        .groupBy('date')
        .orderBy('count_finished', 'desc')
        .orderBy('date', 'desc')
        .as('get_max_date')
    })
})

Answer 1

The following query worked for me: 

.table("task_history")
  .count(db.raw("distinct date"))
  .where('store_id', 100)
  .where('date', '>', function() {
    this.select('date')
      .from(function() {
        this.select('date')
          .table("task_history")
          .first()
          .count('* as count_all')
          .select( db.raw("count(case when finish_time is not null then 1 else null end) as count_finished"))
          .where('store_id', 100)
          .groupBy('date')
          .orderBy('count_finished', 'asc')
          .orderBy('date', 'desc')
          .as('get_max_date')
      })
  });