What it does
Looks up referrals logged in
For each referring customer, pulls the individual referral amount from the log row, the cumulative total from
When a customer made multiple referrals in the window,
client_reward_card_referral_logs within the last nDays calendar days (not open business days — referrals are date-stamped by transaction regardless of schedule).For each referring customer, pulls the individual referral amount from the log row, the cumulative total from
refAmount, and the referred customer's formatted name from refName.When a customer made multiple referrals in the window,
row_number() ordered by updated_at desc keeps the most recent one.
CTEs
rewardsBalance
Current balance per customer
credits − debits, familyID scoped, deleted_at excluded
refAmount
Cumulative referral earnings per referrer
sum(amount) from referral logs grouped by referred_from, scoped to shopID
refName
Formatted name of referred customer
Master name formatting pattern applied to the referred_to customer record
mainQuery
Candidate selection with dedup
Anchors on referral_logs. Dedup by most recent referral date within the window.
Output fields
candidateID
companyID
company
"Name"
"FirstName"
last_name
"FullName"
"Email"
"CustPhone"
"RewardsCard"
"RewardsCardPIN"
"RewardsBalance"
"ReferralAmount"
"CumulativeReferralAmount"
"ReferredName"
"ReferralDate"
"CusType"
"CusGrade"