请求解释筛选信用卡关联用户的Hive SQL查询代码

Got it, let's walk through this Hive SQL query line by line to make sense of what it's doing:

SELECT li.user_id 
FROM loan_info li 
INNER JOIN credit_card_info cci ON li.user_id = cci.user_id 
WHERE CAST(cci.outstanding_balance AS double) = 0.0 
AND datediff(from_unixtime(unix_timestamp(), 'yyyy-MM-dd'), li.last_payment_date) >= 30;

Core Goal #

This query is designed to pull user IDs of people who meet two specific financial criteria: they have no outstanding credit card balance, but haven't made a loan payment in at least 30 days.

Breakdown of Each Part #

• SELECT li.user_id: We're only interested in returning the user ID from the loan_info table (aliased as li for brevity) for matching users.
• FROM loan_info li INNER JOIN credit_card_info cci ON li.user_id = cci.user_id:
  • We're combining two tables: loan_info (holds loan-related data) and credit_card_info (holds credit card data).
  • The INNER JOIN means we only keep rows where a user exists in both tables—so we exclude users who have a loan but no credit card, or vice versa. The ON clause uses user_id to correctly link records for the same user across both tables.
• WHERE CAST(cci.outstanding_balance AS double) = 0.0:
  • This filters for users whose credit card has no remaining debt. The CAST converts the outstanding_balance value to a double data type to ensure we're doing an accurate numeric comparison against 0.0.
• AND datediff(from_unixtime(unix_timestamp(), 'yyyy-MM-dd'), li.last_payment_date) >= 30:
  • This calculates the number of days between today's date (generated by from_unixtime(unix_timestamp(), 'yyyy-MM-dd')) and the user's last loan payment date (li.last_payment_date).
  • We only keep users where this gap is 30 days or more—meaning they haven't made a loan payment in at least a month.

Final Output #

The end result is a list of user IDs that fit both criteria: no credit card debt, but overdue on loan payments by 30+ days.

内容的提问来源于stack exchange，提问作者Naresh Naresh