Below are 3 queries for cars...you will have to change the dates and subcategory cars / bikes (cars for cars...bikes for bikes data)
Adposters
SELECT Date(CAST(ad_create_date AS varchar(10))) as ad_posted_date,
ad_id,poster_email,poster_mobile,city, price,
individual_dealer,
REGEXP_REPLACE(REGEXP_EXTRACT(attributes,'Brand_name:([^\|]*)'),'Brand_name:','') as brand_name,
REGEXP_REPLACE(REGEXP_EXTRACT(attributes,'Model:([^\|]*)'),'Model:','') as model,
REGEXP_REPLACE(REGEXP_EXTRACT(attributes,'Year:([^\|]*)'),'Year:','') as year_of_make,
REGEXP_REPLACE(REGEXP_EXTRACT(attributes,'Kms_Driven:([^\|]*)'),'Kms_Driven:','') as kms_driven,
REGEXP_REPLACE(REGEXP_EXTRACT(attributes,'Fuel_Type:([^\|]*)'),'Fuel_Type:','') as fuel_type
FROM "data_master"."latest_ad_snapshot"
Where regular_noclick='Regular'
and ad_type='offer'
and ad_status NOT IN ('Admin deleted','Unverified Deleted','Delayed Delete')
and ad_new_channel NOT IN ('H2H_Tablet')
and category='Cars & Bikes'
and lower(subcategory) like '%cars%'
AND poster_email <> 'qa_quikr@quikr.com'
and not (poster_email like '%assured%')
AND poster_id NOT IN (155883308,175181991,155883308)
and Date(CAST(ad_create_date AS varchar(10))) >=date('2022-04-09')
Responses
SELECT email_id,mobile, make, model, year_of_registration, kms_driven, price, city FROM "cars_master"."cnb_lead_master"
where lower(lead_type) IN ('chat','call','reply','whatsapp')
and date(date_format(from_unixtime(lead_created_at/1000),'%Y-%m-%d'))>=date('2022-04-09')
and lower(sub_cat_name) like '%cars%'
group by 1,2,3,4,5,6,7,8
or
SELECT email_id,mobile, make, model, year_of_registration, kms_driven, price, city, sub_cat_name FROM "cars_master"."cnb_lead_master"
where lower(lead_type) IN ('chat','call','reply','whatsapp')
and date(date_format(from_unixtime(lead_created_at/1000),'%Y-%m-%d'))<= (current_date - INTERVAL '30' DAY)
and lower(sub_cat_name) like '%bikes%' and make like '%Benelli%'
group by 1,2,3,4,5,6,7,8,9
New cars
SELECT
* FROM "cars_master"."cnb_raw_lead"
where campaign_name IN ('QKR_Price_Quote_Leads','dealeroffers')
and date(cast(cast(date_parse(created_time,'%Y-%d-%m %H:%i:%s') as date) as varchar(10))) between Date('2022-04-01') and Date('2022-04-25')
Note:
- For searching between specific date range:
between Date('2022-06-01') and Date('2022-06-30')
- For searching in past 1 day from current date
<= (current_date - INTERVAL '1' DAY)
