with all_april as(
with card_monthly_recurring as(
  SELECT card_id, card_initial_value
  FROM `just-data.production_tkwy_tenbis.moneycards` 
  where 
  card_pre_paid!=TRUE 
  and
  UsesDateTimeLimitations!=True
  and
  check_monthly_limit=True),

transactions_april as(
  SELECT cardid, sum(mc_value) as mc_value_april
  from `just-data-warehouse.staging_core.corporate_transactions`
  where refundreferenceid is null
  and ancestorid not in (select distinct refundreferenceid from `just-data-warehouse.staging_core.corporate_transactions`
  where refundreferenceid is not null)
  and   moneycards_mockobject ='0'
  and DATE(date_trunc(transactiontime, day))>='2024-04-01' and DATE(date_trunc(transactiontime, day))<'2024-05-01'
  group by 1 
  order by 1 desc
)
select card_monthly_recurring.*, round(transactions_april.mc_value_april,2) as mc_value
from card_monthly_recurring
inner join transactions_april
on transactions_april.cardid=card_monthly_recurring.card_id
where ROUND(mc_value_april,2)>round(card_initial_value,2))
select count(distinct all_april.card_id) as moneycards,
       ROUND(sum(card_initial_value),2) as monthly_limit,
       ROUND(sum(mc_value),2) as total_spent,
      ROUND((sum(mc_value)/ sum(card_initial_value))*100, 2) as allowance_spent
from all_april