你真的不应该发布屏幕截图，因为那些想要帮助的人也不想输入你的数据。将来请提供必要的数据，其他人可以将其剪切粘贴到SQLPLUS中
话虽如此，我相信数据应该意味着该账户从2022-12-27到2023-01-09是合格的;在2023-01-10，帐户将不合格。
这里有一些你可以根据需要调整的东西。

CREATE TABLE invoices(customer_id, invoice_id, date_from, date_to) AS
SELECT 123, 229,DATE '2023-01-01', DATE '2023-01-30' FROM DUAL 

CREATE TABLE discount_history(customer_id, discount_date, discount_status) AS
SELECT 123, DATE '2022-12-27', 'ELIGIBLE' FROM DUAL UNION ALL 
SELECT 123, DATE '2023-01-10', 'INELIGIBLE' FROM DUAL UNION ALL 
SELECT 123, DATE '2023-01-19', 'ELIGIBLE' FROM DUAL UNION ALL 
SELECT 123, DATE '2023-01-27', 'INELIGIBLE' FROM DUAL

with
  prep (customer_id, discount_date, discount_status, discount_end_date) as (
    select customer_id, discount_date, discount_status, 
           lead(discount_date, 1, date '2999-12-31')
                over (partition by customer_id order by discount_date) - 1
    from   discount_history
  )
select i.customer_id,
       sum(least(i.date_to, p.discount_end_date) - greatest(i.date_from, p.discount_date) + 1)
         as discount_days
 from   invoices i join prep p
         on    i.customer_id = p.customer_id
           and p.discount_status = 'ELIGIBLE'
           and i.date_from <= p.discount_end_date 
           and i.date_to   >= p.discount_date
group   by i.customer_id
;

customer_id  discount_days
123                  17

字符串

如果您需要考虑可能的数据质量问题，例如连续几个相同的discount_status：

with merged_history(customer_id, start_dat, end_dat) as (
    select * from discount_history
    match_recognize (
        partition by customer_id
        order by discount_date,  discount_status
        measures first(eligible.discount_date) as start_eligible, 
            first(ineligible.discount_date) as end_inineligible
        pattern( eligible+ ineligible+ )
        define
            eligible as discount_status = 'ELIGIBLE',
            ineligible as discount_status = 'INELIGIBLE'
    )
)
select inv.customer_id,
    sum(least(inv.date_to, hist.end_dat) - greatest(inv.date_from, hist.start_dat)) as discount_days
from invoices inv
join merged_history hist on inv.customer_id = hist.customer_id;


123 17

字符串