"top 100 distinct customers with single biggest transactions"

gistfile1.py

from sqlalchemy import Integer, String, create_engine, Column, func, ForeignKey
from sqlalchemy.orm import Session
from sqlalchemy.ext.declarative import declarative_base
import random

Base = declarative_base()

class Customer(Base):
    __tablename__ = 'customer'

    id = Column(Integer, primary_key=True)
    name = Column(String)

class CustomerTransaction(Base):
    __tablename__ = 'customer_transaction'
    id = Column(Integer, primary_key=True)
    customer = Column(Integer, ForeignKey('customer.id'))
    transaction = Column(String)
    value = Column(Integer)

e = create_engine("sqlite://")
Base.metadata.create_all(e)

session = Session(e)

customers = [Customer(name="Customer %d" % i) for i in xrange(100)]
session.add_all(customers)
session.flush()

for i in xrange(1000):
    session.add(
        CustomerTransaction(
            customer=random.choice(customers).id,
            transaction="trans%d" % random.randint(1, 1000),
            value=random.randint(100, 500)
        )
    )
session.commit()

e.echo = True
subq = session.query(
                    CustomerTransaction.customer,
                    func.max(CustomerTransaction.value).label('value')
            ).\
            group_by(CustomerTransaction.customer).\
            subquery()

customers = session.query(Customer, subq.c.value).\
            join(subq, Customer.id == subq.c.customer).\
            order_by(subq.c.value.desc()).\
            limit(100).all()

print "; ".join("Name: %s, value %s" % (c.name, value)
                    for c, value in customers)