Can I get some help defining an sqlite query?

Question

I have the following data table:

df = pd.DataFrame({'Well': ['A', 'A', 'B', 'B'],
                   'BP': [380., 25., 24., 360.],
                   'ng': [1., 10., 1., 10.],
                  'Band A': [True, False, False, True],
                  'Band B': [False, True, True, False]})

I need help creating an sql query that returns true if, within "Well", the following conditions are met: "Band A" is "True" and "ng" for "Band A" is greater than "ng" for "Band B"

I made the following unsuccessful attempt, and am stuck:

sqlcmd = '''
SELECT
    Well,
    ng,
    CASE
        WHEN Band_A = True AND ng > (SELECT ng FROM df WHERE Band_B = True) THEN 'True'
        ELSE 'False'
    END AS Duplicate
FROM df
ORDER BY Well;'''

pp.pprint(pysqldf(sqlcmd).head())

Answer 1

I skip Pandas and demonstrate how to do this in pure SQLite. It should be translatable.

import os
import sqlite3

db = sqlite3.connect('76766531.db')

db.executescript('''
create table bands(
  well varchar(1) not null,
  bp int not null,
  ng int not null,
  band_a boolean not null,
  band_b boolean not null
);

insert into bands(well, bp, ng, band_a, band_b) values
('A', 380,  1, true, false),
('A',  25, 10, false, true),
('B',  24,  1, false, true),
('B', 360, 10, true, false);
''').close()

cur = db.execute('''
select side_a.well,
       side_a.ng > side_b.ng as predicate
from bands as side_a
join bands as side_b
    on side_a.well = side_b.well
   and side_a.band_a
   and side_b.band_b;
''')
try:
    for well, predicate in cur.fetchall():
        print(f'{well}: {predicate == 1}')
finally:
    cur.close()

A: False
B: True