There are many issues with your query.
I will explain some and this may improve your query
str_to_date(date_format(sysdate(),'%d-%b-%Y'),'%d-%b-%Y') - max(ren_date) days
SELECT str_to_date(date_format(sysdate(),'%d-%b-%Y'),'%d-%b-%Y');
SELECT DATE(NOW());
SELECT "2018-07-09"-"2018-07-02";
SELECT DATEDIFF("2018-07-09", "2018-07-02");
SELECT
a
, b
, '0' as zero
, 'd' as d
FROM (
SELECT
aa as a
, MAX(bb) as b
FROM
table_tbl
GROUP BY a
HAVING b > 7
)
If you have any further question, please let me know.