"""Two one-to-many children; independent sums before joins, SQLite memory only."""
import sqlite3,json
def demo():
    c=sqlite3.connect(':memory:');c.executescript('CREATE TABLE request(id INTEGER PRIMARY KEY);CREATE TABLE note(id INTEGER PRIMARY KEY,request_id INTEGER,amount INTEGER);CREATE TABLE attempt(id INTEGER PRIMARY KEY,request_id INTEGER,seconds INTEGER);')
    c.executemany('INSERT INTO request VALUES(?)',[(1,),(2,)]);notes=[(11,1,10),(12,1,10)];attempts=[(21,1,2),(22,1,3),(23,1,4)];c.executemany('INSERT INTO note VALUES(?,?,?)',notes);c.executemany('INSERT INTO attempt VALUES(?,?,?)',attempts)
    raw='FROM request r LEFT JOIN note n ON n.request_id=r.id LEFT JOIN attempt a ON a.request_id=r.id'
    joined=[list(r)for r in c.execute('SELECT r.id,n.id,n.amount,a.id,a.seconds '+raw+' WHERE r.id=1 ORDER BY n.id,a.id')];assert len(joined)==6
    wrong=[list(r)for r in c.execute('SELECT r.id,COUNT(*),COUNT(n.id),COUNT(a.id),SUM(n.amount),SUM(a.seconds) '+raw+' GROUP BY r.id ORDER BY r.id')]
    distinct=[list(r)for r in c.execute('SELECT r.id,COUNT(DISTINCT n.id),COUNT(DISTINCT a.id),SUM(DISTINCT n.amount),SUM(DISTINCT a.seconds) '+raw+' GROUP BY r.id ORDER BY r.id')]
    query='''WITH ns AS(SELECT request_id,COUNT(*) AS n,SUM(amount) AS amount FROM note GROUP BY request_id), ats AS(SELECT request_id,COUNT(*) AS n,SUM(seconds) AS seconds FROM attempt GROUP BY request_id) SELECT r.id,COALESCE(ns.n,0),COALESCE(ats.n,0),ns.amount,ats.seconds FROM request r LEFT JOIN ns ON ns.request_id=r.id LEFT JOIN ats ON ats.request_id=r.id ORDER BY r.id'''
    correct=[list(r)for r in c.execute(query)];oracle=[[1,2,3,sum(x[2]for x in notes),sum(x[2]for x in attempts)],[2,0,0,None,None]]
    assert wrong==[[1,6,6,6,60,18],[2,1,0,0,None,None]]and distinct==[[1,2,3,10,9],[2,0,0,None,None]]and correct==oracle
    c.close();return {'sqlite_version':sqlite3.sqlite_version,'authored_requests':[1,2],'authored_notes':[list(x)for x in notes],'authored_attempts':[list(x)for x in attempts],'six_actual_joined_rows_for_parent1':joined,'wrong_columns':['request','count_star','count_notes','count_attempts','sum_note_amount','sum_attempt_seconds'],'wrong':wrong,'distinct_columns':['request','distinct_note_ids','distinct_attempt_ids','distinct_amount','distinct_seconds'],'distinct_not_general_sum_fix':distinct,'correct_columns':['request','note_count','attempt_count','note_amount','attempt_seconds'],'preaggregate_query':query,'preaggregated':correct,'independent_python_oracle':oracle,'null_policy':'No-child counts use explicit0; no-child sums kept NULL, not asserted measured0. Both child amounts10 are independent rows and must both contribute.','scope':'No runtime benchmark, CRM data or app architecture claim. Amount integers are authored units, not money or real sales.'}
if __name__=='__main__':print(json.dumps(demo(),indent=2))