what could be alternate dataframe solution for following query?

res1= spark.sql("""select cd.c_number , cd.full_name, cd.c_limit, cd.c_limit-d.totaldebitamount+c.totalcreitamount as outstandinglimit,
                cd.c_limit-d.totaldebitamount as outstanding_amount
                from card cd
                left join
                (select c_number, sum(amt_spend) as totaldebitamount from debit_details where d_status="Success" group by c_number) d
                on cd.c_number = d.c_number
                left join
                (select c_number, sum(amt_paid) as totalcreitamount from credit_details where c_status="success" group by c_number) c
                on cd.c_number = c.c_number
                where cd.act_flag = "A" and cd.c_limit-d.totaldebitamount+c.totalcreitamount <> cd.c_limit;
                """)

res1.show()