convert sql query to python pandas

-- BB Registered Downgrades and Churn
SELECT ref1.eff_bgn_dt AS eff_bgn_dt
--,CASE WHEN ref1.hbo_mx_subsrptn_actv_ind = 'Y' THEN 'Registered' ELSE 'Un-Registered' END "Sub Type"
, case when ref1.hbo_mx_first_athntct_dt < '2021-09-01' and ref1.hbo_mx_first_athntct_dt >= '2020-05-27' then 'ACTIVA' else 'NON-ACTIVA' end as actv_ind
,hbo_mx_first_athntct_dt
,ref1.blng_acct_nbr
,ref1.acct_key
,COUNT(
    DISTINCT ref1.acct_key
  ) AS Total_ACTIVE
,COUNT(
    DISTINCT CASE WHEN hsia_actv_ind = 'Y' AND ref2.acct_key IS  NULL THEN ref1.acct_key end
  ) AS Total_Downgrade 
  ,COUNT(
    DISTINCT CASE WHEN hsia_actv_ind = 'N' AND ref2.acct_key IS  NULL THEN ref1.acct_key  end
  ) AS Total_Churn FROM (
SELECT 
          DISTINCT aux.acct_key AS acct_key, aux.blng_acct_nbr, dim.prd_cat_cd,dt.dt_key AS eff_bgn_dt,sub_chort_nm,
                                          aux.hbo_mx_subsrptn_actv_ind, aux.hbo_mx_first_athntct_dt, aux.DATA_DT
        FROM 
          hbo_mx_acct_aux_dim_spsh aux
          JOIN hbo_mx_acct_subsrptn_prd_fct fct -- active subscription
              ON aux.acct_key = fct.acct_key 
              AND aux.bill_sys_geo_id = fct.bill_sys_geo_id 
              AND fct.new_subsrptn_prd_sts_cd IN ('ACTV', 'A', 'SUSP') 
              JOIN
            hbo_mx_subsrptn_prd_offr_dim dim -- Broadband prd offr
            ON 
              fct.subsrptn_prd_offr_key = dim.subsrptn_prd_offr_key
              AND dim.bill_sys_geo_id   = 24
              AND dim.prd_cat_cd IN ('IA')
              --IN ('IA','FB')
           JOIN dt_dim dt ON (dt.dt_key BETWEEN fct.eff_bgn_dt AND fct.eff_end_dt)
           WHERE aux.bill_sys_geo_id = 24
              AND aux.hbo_mx_actl_prc_desc IN 
                    ( '1-INCLUDED', 
                      '2-FREE TRIAL', '6-PAID') -- To Identify Eligible Customers      
AND aux.hsia_actv_ind = 'Y'
AND dt.dt_key >= '2020-05-27' AND dt.dt_key <= '2021-08-31'
AND aux.DATA_DT = dt.dt_key
) ref1
LEFT JOIN
(
SELECT
          DISTINCT aux.acct_key AS acct_key, dim.prd_cat_cd,dt.dt_key AS eff_bgn_dt,sub_chort_nm,aux.DATA_DT
        FROM 
          hbo_mx_acct_aux_dim_spsh aux
          JOIN hbo_mx_acct_subsrptn_prd_fct fct -- active subscription
              ON aux.acct_key = fct.acct_key 
              AND aux.bill_sys_geo_id = fct.bill_sys_geo_id 
              AND fct.new_subsrptn_prd_sts_cd IN ('ACTV', 'A', 'SUSP') 
              JOIN 
            hbo_mx_subsrptn_prd_offr_dim dim -- Boradband prd offr
            ON 
              fct.subsrptn_prd_offr_key = dim.subsrptn_prd_offr_key
              AND dim.bill_sys_geo_id   = 24
              AND dim.prd_cat_cd IN ('IA')
              --IN ('IA','FB')

              JOIN dt_dim dt ON (dt.dt_key BETWEEN fct.eff_bgn_dt AND fct.eff_end_dt)
           WHERE aux.bill_sys_geo_id          = 24
              AND aux.hbo_mx_actl_prc_desc  IN 
                    ( '1-INCLUDED', 
                      '2-FREE TRIAL', '6-PAID') -- To Identify Eligible Customers      
AND aux.hsia_actv_ind = 'Y'
AND dt.dt_key >= '2020-05-27' AND dt.dt_key <= '2021-08-31'
AND aux.DATA_DT = dt.dt_key
) ref2 ON (ref1.eff_bgn_dt + 1 = ref2.eff_bgn_dt AND ref1.acct_key = ref2.acct_key)
join
(
SELECT 
        acct_key, 
        ban, 
        acct_sts_desc, 
        wirls_actv_ind, 
        hsia_actv_ind
        FROM 
          acct_dim 
        WHERE 
          bill_sys_geo_id = 24
) ad
ON ad.acct_key = ref1.acct_key
where ref1.eff_bgn_dt = '2021-08-31'
GROUP BY 1,2,3,4,5

select a.passengerId passA,b.passengerId passB,count(*) numflightsTogether from df a inner join df  b on (a.flightid=b.flightid and a.date=b.date ) where a.passengerid != b.passengerid  group by a.passengerid,b.passengerid having count(*) >3