import model

model.connect()
model.cursor.execute('''
        SELECT 
            username, 
            SUM(CASE -- sum score for each of the users ownables
                WHEN ownership.ownable_id = ? THEN ownership.amount
                ELSE ownership.amount * (SELECT price 
                                         FROM transactions
                                         WHERE ownable_id = ownership.ownable_id 
                                         ORDER BY dt DESC 
                                         LIMIT 1)
                END
            ) score
        FROM users, ownership
        WHERE ownership.user_id = users.rowid
        AND users.username != 'bank'
        GROUP BY users.rowid
        ''', (model.currency_id(),))

print(model.cursor.fetchall())