SQL Count In Range

How could I count data in range which could be configured
Something like this,

CAR_AVBL
+--------+-----------+  
| CAR_ID | DATE_AVBL |  
+--------------------|  
|  JJ01  |    1      |  
|  JJ02  |    1      |  
|  JJ03  |    3      |  
|  JJ04  |    10     |  
|  JJ05  |    13     |  
|  JJ06  |    4      |  
|  JJ07  |    10     |  
|  JJ08  |    1      |  
|  JJ09  |    23     |  
|  JJ10  |    11     |  
|  JJ11  |    20     |  
|  JJ12  |    3      |  
|  JJ13  |    19     |  
|  JJ14  |    22     |  
|  JJ15  |    7      |  
+--------------------+  

ZONE_CFG
+--------+------------+
|  DATE  | ZONE_DESCR |
+--------+------------+
|   15   | GREEN_ZONE |
|   25   | YELLOW_ZONE|
|   30   | RED_ZONE   |
+--------+------------+

Table ZONE_CFG is configurable, so I could not use static value for this
The DATE column mean maximum date for each ZONE

And the result what I expected :

+------------+----------+
| ZONE_DESCR | AVBL_CAR |
+------------+----------+
| GREEN_ZONE |    11    |
| YELLOW_ZONE|    4     |
| RED_ZONE   |    0     |
+------------+----------+

Please could someone help me with this

Answer

You can use LAG and group by as following:

SELECT
    ZC.ZONE_DESCR,
    COUNT(1) AS AVBL_CAR
FROM
    CAR_AVBL CA
    JOIN ( SELECT
            ZONE_DECR,
            COALESCE(LAG(DATE) OVER(ORDER BY DATE) + 1, 0) AS START_DATE,
            DATE AS END_DATE
        FROM ZONE_CFG ) ZC 
    ON ( CA.DATE_AVBL BETWEEN ZC.START_DATE AND ZC.END_DATE )
GROUP BY
    ZC.ZONE_DESCR;

Note: Don’t use oracle preserved keywords (DATE, in your case) as the name of the columns. Try to change it to something like DATE_ or DATE_START or etc..

Cheers!!