Ggtcf

I am trying to get twitter data of narendramodi using below command.

SELECT b.t_id,a.profile_image,b.tweet_text,e.media_media_url,b.retweet_count,b.favorite_count as like_count,count(reply_to_status_id) as reply_count,f.tweet_impression_count,f.tweet_engagement_count,f.tweet_engagement_rate 
FROM twitter_users a LEFT JOIN twitter_tweets b on a.user_id=b.user_id 
LEFT JOIN twitter_tweet_replies c on b.t_id = c.t_id 
LEFT JOIN twitter_tweet_media e on b.t_id = e.t_id 
LEFT JOIN twitter_tweet_metric_aggregates f on f.metric_timestamp= 
(select max(metric_timestamp) FROM twitter_tweet_metric_aggregates g 
WHERE g.t_id=f.t_id and g.t_id=b.t_id) 
WHERE a.twitter_screen_name= 'narendramodi' 
GROUP BY b.t_id,a.profile_image 
,b.tweet_text,b.retweet_count,b.favorite_count,
e.media_media_url,f.imp_count,f.eng_count,f.eng_rate);

Query was working correctly But, in the above query I have used sub-select to get recent data of imp_counts based on timestamp. Because of that Query_cost was huge and so it was taking more than 15min for query execution. I want to reduce that and should able to execute within 10seconds. For that reason I was trying to use WITH (CTE) expression.

Here it is

WITH metric_counts AS (
SELECT max(metric_timestamp),f.t_id 
FROM twitter_tweet_metric_aggregates f LEFT JOIN twitter_tweets b on 
f.t_id=b.t_id 
)
SELECT 
b.t_id,a.profile_image,b.tweet_text,e.media_media_url,b.retweet_count
,b.favorite_count as like_count, count(reply_to_status_id) as 
reply_count,metric_counts.imp_count
,metric_counts.eng_count,metric_counts.eng_rate 

FROM twitter_users as a 
LEFT JOIN twitter_tweets as b on a.twitter_user_id=b.twitter_user_id 
LEFT JOIN twitter_tweet_replies c on b.t_id = c.t_id 
LEFT JOIN twitter_tweet_media e on b.t_id = e.t_id 
LEFT JOIN metric_counts on metric_counts.t_id = b.t_id WHERE 
lower(a.twitter_screen_name)=lower('narendramodi') 
GROUP BY b.t_id,a.profile_image,b.tweet_text,e.media_media_url,
b.retweet_count,b.favorite_count, 
metric_counts.imp_count,metric_counts.eng_count,
metric_counts.eng_rate;

The query was executing but it is not giving imp_count,eng_count,eng_rate values from that particular table.

NOTE : If I remove that sub-select query was executing within 10sec.But I want that also to be included. For that I have used WITH but failing. So can anyone help me out on how to write first query using WITH expression.