iF`dZdedefdZdZdZdZdZdZd Z d Z d Z d Z d Z dZdZdZdZdZdZdZdZy)zb SQL queries for user-centric profiling. Read-only; all temporal queries use start_date/end_date. nreturncL|dkr tddjdg|zS)zZReturns ?, ?, ... (n times) for IN (...). ``n`` must be >= 1 (empty IN () is invalid SQL).z;in_placeholders requires n > 0 (empty IN lists are invalid)z, ?) ValueErrorjoin)rs C/var/www/html/userprofiledev.eatanceapp.com/data_fetcher/queries.pyin_placeholdersr s*AvVWW 99cUQY z SELECT * FROM users WHERE id = ?z; SELECT TOP (?) id FROM users WHERE id > ? ORDER BY id ASC z SELECT TOP (?) u.id FROM users u WHERE u.id > ? AND EXISTS ( SELECT 1 FROM dbo.user_activity_logs a WHERE a.user_id = u.id AND a.created_at >= DATEADD(day, -?, GETDATE()) ) ORDER BY u.id ASC z% SELECT COUNT_BIG(*) AS n FROM users z SELECT COUNT_BIG(*) AS n FROM users u WHERE EXISTS ( SELECT 1 FROM dbo.user_activity_logs a WHERE a.user_id = u.id AND a.created_at >= DATEADD(day, -?, GETDATE()) ) z SELECT CAST( CASE WHEN EXISTS ( SELECT 1 FROM dbo.user_activity_logs WHERE user_id = ? AND created_at >= DATEADD(day, -?, GETDATE()) ) THEN 1 ELSE 0 END AS int) AS has_activity zl SELECT * FROM user_activity_logs WHERE user_id = ? AND created_at BETWEEN ? AND ? ORDER BY created_at DESC af SELECT om.*, rd.restaurant_name, coa.area AS order_area, coa.city AS order_city FROM restaurant_order_master om LEFT JOIN restaurant_details rd ON rd.restaurant_id = om.restaurant_id LEFT JOIN customer_orders_address coa ON coa.order_id = om.id WHERE om.user_id = ? AND om.is_deleted = 0 AND om.created_at BETWEEN ? AND ? ORDER BY om.created_at DESC z SELECT oi.*, rd.restaurant_name FROM order_item_details oi LEFT JOIN restaurant_details rd ON rd.restaurant_id = oi.restaurant_id WHERE oi.order_id IN ({}) z[ SELECT * FROM restaurant_impression_logs WHERE user_id = ? AND created_at BETWEEN ? AND ? a" SELECT r.*, rt.rating, rd.restaurant_name FROM reviews r LEFT JOIN ratings rt ON rt.review_id = r.id LEFT JOIN restaurant_details rd ON rd.restaurant_id = r.restaurant_id WHERE r.user_id = ? AND r.is_deleted = 0 AND r.status = 1 AND r.created_at BETWEEN ? AND ? ORDER BY r.created_at DESC z SELECT cm.*, rd.restaurant_name FROM cart_master cm LEFT JOIN restaurant_details rd ON rd.restaurant_id = cm.restaurant_id WHERE cm.user_id = ? AND cm.created_at BETWEEN ? AND ? ORDER BY cm.created_at DESC zW SELECT c.dietary_preferences, c.special_requests FROM customers c WHERE c.user_id = ? z7SELECT slug FROM dietary_preferences WHERE slug IN ({})ap SELECT acl.item_id, ISNULL(mid.item_name, 'Unknown') AS item_name, COUNT(*) AS total_cart_events_in_window FROM add_to_cart_logs acl LEFT JOIN menu_items_details mid ON mid.menu_item_id = acl.item_id AND mid.language_id = 1 WHERE acl.user_id = ? AND acl.created_at BETWEEN ? AND ? GROUP BY acl.item_id, mid.item_name ORDER BY total_cart_events_in_window DESC a SELECT ISNULL(CAST(total_add_to_cart_events_in_window AS FLOAT) / NULLIF(total_items_ordered_in_window, 0), 0) AS add_to_cart_events_per_item_ordered, count_distinct_items_added_but_never_ordered, count_distinct_items_added_then_removed_from_cart, total_add_to_cart_events_in_window, total_items_ordered_in_window FROM ( SELECT (SELECT COUNT(*) FROM add_to_cart_logs a WHERE a.user_id = ? AND a.created_at BETWEEN ? AND ? AND LOWER(LTRIM(RTRIM(ISNULL(a.action, '')))) = 'add') AS total_add_to_cart_events_in_window, (SELECT COUNT(*) FROM order_item_details oi INNER JOIN restaurant_order_master rom ON rom.id = oi.order_id WHERE rom.user_id = ? AND rom.is_deleted = 0 AND rom.created_at BETWEEN ? AND ?) AS total_items_ordered_in_window, (SELECT COUNT(DISTINCT acl.item_id) FROM add_to_cart_logs acl WHERE acl.user_id = ? AND acl.created_at BETWEEN ? AND ? AND LOWER(LTRIM(RTRIM(ISNULL(acl.action, '')))) = 'add' AND NOT EXISTS ( SELECT 1 FROM order_item_details oi INNER JOIN restaurant_order_master rom ON rom.id = oi.order_id WHERE rom.user_id = ? AND rom.is_deleted = 0 AND rom.created_at BETWEEN ? AND ? AND oi.menu_item_id = acl.item_id )) AS count_distinct_items_added_but_never_ordered, (SELECT COUNT(*) FROM ( SELECT acl.item_id FROM add_to_cart_logs acl WHERE acl.user_id = ? AND acl.created_at BETWEEN ? AND ? AND LOWER(LTRIM(RTRIM(ISNULL(acl.action, '')))) = 'add' INTERSECT SELECT acl2.item_id FROM add_to_cart_logs acl2 WHERE acl2.user_id = ? AND acl2.created_at BETWEEN ? AND ? AND LOWER(LTRIM(RTRIM(ISNULL(acl2.action, '')))) = 'remove' ) t) AS count_distinct_items_added_then_removed_from_cart ) s a SELECT TOP 10 acl.item_id, ISNULL(mid.item_name, 'Unknown') AS item_name, COUNT(*) AS times_added_to_cart_but_never_ordered FROM add_to_cart_logs acl LEFT JOIN menu_items_details mid ON mid.menu_item_id = acl.item_id AND mid.language_id = 1 WHERE acl.user_id = ? AND acl.created_at BETWEEN ? AND ? AND LOWER(LTRIM(RTRIM(ISNULL(acl.action, '')))) = 'add' AND NOT EXISTS ( SELECT 1 FROM order_item_details oi INNER JOIN restaurant_order_master rom ON rom.id = oi.order_id WHERE rom.user_id = ? AND rom.is_deleted = 0 AND rom.created_at BETWEEN ? AND ? AND oi.menu_item_id = acl.item_id ) GROUP BY acl.item_id, mid.item_name ORDER BY times_added_to_cart_but_never_ordered DESC a SELECT ISNULL(cd.title, 'Unknown') AS category_name, COUNT(DISTINCT acl.item_id) AS distinct_items_added_but_never_ordered_in_this_category FROM add_to_cart_logs acl INNER JOIN menu_items mi ON mi.id = acl.item_id LEFT JOIN category_details cd ON cd.category_id = mi.category_id AND cd.language_id = 1 WHERE acl.user_id = ? AND acl.created_at BETWEEN ? AND ? AND LOWER(LTRIM(RTRIM(ISNULL(acl.action, '')))) = 'add' AND NOT EXISTS ( SELECT 1 FROM order_item_details oi INNER JOIN restaurant_order_master rom ON rom.id = oi.order_id WHERE rom.user_id = ? AND rom.is_deleted = 0 AND rom.created_at BETWEEN ? AND ? AND oi.menu_item_id = acl.item_id ) GROUP BY cd.title, mi.category_id ORDER BY distinct_items_added_but_never_ordered_in_this_category DESC N)__doc__intstrr QUERY_USER_BASIC_DETAILSQUERY_USER_IDS_KEYSET/QUERY_USER_IDS_KEYSET_WITH_RECENT_ACTIVITY_LOGSQUERY_COUNT_ALL_USER_IDS)QUERY_COUNT_USER_IDS_WITH_RECENT_ACTIVITY!QUERY_USER_HAS_ACTIVITY_LOG_SINCEQUERY_ACTIVITY_LOGS QUERY_ORDERSQUERY_ORDER_ITEMSQUERY_IMPRESSIONSQUERY_REVIEWS_AND_RATINGSQUERY_CART_DATAQUERY_DIETARY_PREFERENCESQUERY_DIETARY_PREFERENCE_LABELSQUERY_CART_ITEMS_IN_WINDOWQUERY_CART_STATS$QUERY_CART_TOP_CONSIDERED_NOT_BOUGHT,QUERY_CART_CONSIDERED_NOT_BOUGHT_BY_CATEGORYr r r"s  s s > 3/ -)%!   #\ $N($,0,r