Question: Is there any method to reduce the time taken to get the result from below query?
Please help. Thanks in advance!

```
select status, count(distinct id)
from emp
where id >=
      ( select min(id)
        from emp
        where id >= (select max(id-200000) from emp)
           and trunc(join_date) >= '01-Mar-2018')
group by status;

```

Answer: 
Use analytic functions - this will perform only a single table scan (whereas your query has three table/index scans):

```
SELECT status,
       COUNT( DISTINCT id )
FROM   (
  SELECT status,
         id,
         MIN( CASE WHEN join_date >= DATE '2018-03-01' THEN id END ) OVER () AS min_id
  FROM   (
    SELECT status,
           id,
           join_date,
           MAX( id ) OVER () AS max_id
    FROM   emp
  )
  WHERE  id >= max_id - 20000
)
WHERE  id >= min_id
GROUP BY status;

```

Also, you can use a date literal (rather than relying on implicit conversion of a string to a date using the `NLS_DATE_FORMAT` session parameter) and you do not need to use the `TRUNC()` function (since that may prevent Oracle using an index on the `join_date` column and would instead require a function-based index).
It is important to know if id is a primary key (as columns with that name usually are) or not. If it is not, you definitely need an index on id for it to perform (and I would also wonder what the purpose of the column was). If id is the primary key, you don't need to the `distinct` as the values will be unique anyway.

The `select min(id)` sub-select is redundant as you already found max(id - 200000) so you don't need to know the first min(id) greater than that. You can just use >= by itself (with the condition on the date added). By the way, I would write `max(id) - 200000` instead; on some databases, it might work better.

The date comparison may be problematic. You should try an index on `join_date` if you haven't got one already, but the `trunc` might stop that from being used, so it would be best to remove that and make the other side of the compare use a `TO_TIMESTAMP` or `TO_DATE` to generate a corresponding literal as appropriate, setting the time to midnight. 

But there can be problems with comparing timestamps due to timezones, etc. I'd need to know more about your setup to know whether that is likely to be a problem.