Breaking News: Grepper is joining You.com. Read the official announcement!

convert sql query to python pandas

Thankful Turkey answered on September 17, 2021 Popularity 1/10 Helpfulness 1/10

answer convert sql query to python pandas

related convert sql query to python pandas

convert sql query to python pandas

Comment

-- 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

Popularity 1/10 Helpfulness 1/10 Language sql

Source: Grepper

Tags: pandas python sql

Link to this answer
Share Copy Link

Contributed on Nov 10 2021

Thankful Turkey

0 Answers Avg Quality 2/10

Closely Related Answers

convert sql query to python pandas

Comment

select *
from table1 inner join table on table1.cc = table2.cc and table1.kc = table2.kc and table1.nn = table2.nn
where table1.date <> table2.date

Popularity 1/10 Helpfulness 1/10 Language sql

Source: Grepper

Tags: pandas python sql

Link to this answer
Share Copy Link

Contributed on Sep 17 2021

Helpless Hyena

0 Answers Avg Quality 2/10

convert sql query to python pandas

Comment

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

Popularity 1/10 Helpfulness 1/10 Language sql

Source: Grepper

Tags: pandas python sql

Link to this answer
Share Copy Link

Contributed on Oct 02 2021

Cruel Copperhead

0 Answers Avg Quality 2/10

convert sql query to python pandas

Contents

More Related Answers

convert sql query to python pandas

Closely Related Answers

convert sql query to python pandas

convert sql query to python pandas

Grepper

Documentation

Social

Legal

Contact

Oops, You will need to install Grepper and log-in to perform this action.