def leaderboard():
    """
        Get a table with users sorted by rating
    """

    specific_institute = False
    atable = db.auth_user
    cftable = db.custom_friend

    global_leaderboard = False
    if request.vars.has_key("global"):
        if request.vars["global"] == "True":
            global_leaderboard = True
        else:
            if session.user_id is None:
                response.flash = "Login to see Friends Leaderboard"
                global_leaderboard = True
    else:
        if session.user_id is None:
            global_leaderboard = True

    heading = "Global Leaderboard"
    afields = ["first_name", "last_name", "stopstalk_handle",
               "institute", "per_day", "rating"]
    cfields = afields + ["duplicate_cu"]

    aquery = (atable.id > 0)
    cquery = (cftable.id > 0)
    if global_leaderboard is False:
        if session.user_id:
            heading = "Friends Leaderboard"
            friends, cusfriends = utilities.get_friends(session.user_id)
            custom_friends = [x[0] for x in cusfriends]

            # Add logged-in user to leaderboard
            friends.append(session.user_id)
            aquery &= (atable.id.belongs(friends))
            cquery &= (cftable.id.belongs(custom_friends))
        else:
            aquery &= (1 == 0)
            cquery &= (1 == 0)

    if request.vars.has_key("q"):
        heading = "Institute Leaderboard"
        institute = request.vars["q"]

        if institute != "":
            specific_institute = True
            aquery &= (atable.institute == institute)
            cquery &= (cftable.institute == institute)
            reg_users = db(aquery).select(*afields)
            custom_users = db(cquery).select(*cfields)

    if specific_institute is False:
        reg_users = db(aquery).select(*afields)
        custom_users = db(cquery).select(*cfields)

    # Find the total solved problems(Lesser than total accepted)
    solved_count = {}
    sql = """
             SELECT stopstalk_handle, COUNT(DISTINCT(problem_name))
             FROM submission
             WHERE status = 'AC'
             GROUP BY user_id, custom_user_id;
          """
    tmplist = db.executesql(sql)
    for user in tmplist:
        solved_count[user[0]] = user[1]

    complete_dict = {}
    # Prepare a list of stopstalk_handles of the
    # users relevant to the requested leaderboard
    friends_stopstalk_handles = []
    for x in reg_users:
        friends_stopstalk_handles.append("'" + x.stopstalk_handle + "'")
        complete_dict[x.stopstalk_handle] = []

    for custom_user in custom_users:
        stopstalk_handle = custom_user.stopstalk_handle
        if custom_user.duplicate_cu:
            stopstalk_handle = cftable(custom_user.duplicate_cu).stopstalk_handle
        friends_stopstalk_handles.append("'" + stopstalk_handle + "'")
        complete_dict[stopstalk_handle] = []

    if friends_stopstalk_handles == []:
        friends_stopstalk_handles = ["-1"]

    # Build the complex SQL query
    sql_query = """
                    SELECT stopstalk_handle, DATE(time_stamp), COUNT(*) as cnt
                    FROM submission
                    WHERE stopstalk_handle in (%s)
                    GROUP BY stopstalk_handle, DATE(submission.time_stamp)
                    ORDER BY time_stamp;
                """ % (", ".join(friends_stopstalk_handles))

    user_rows = db.executesql(sql_query)
    for user in user_rows:
        if complete_dict[user[0]] != []:
            complete_dict[user[0]].append((user[1], user[2]))
        else:
            complete_dict[user[0]] = [(user[1], user[2])]

    users = []
    for user in reg_users:
        try:
            solved = solved_count[user.stopstalk_handle]
        except KeyError:
            solved = 0

        tup = utilities.compute_row(user, complete_dict, solved)
        if tup is not ():
            users.append(tup)

    for user in custom_users:
        try:
            if user.duplicate_cu:
                orig_user = cftable(user.duplicate_cu)
                solved = solved_count[orig_user.stopstalk_handle]
            else:
                solved = solved_count[user.stopstalk_handle]
        except KeyError:
            solved = 0
        tup = utilities.compute_row(user, complete_dict, solved, True)
        if tup is not ():
            users.append(tup)

    # Sort users according to the rating
    users = sorted(users, key=lambda x: x[3], reverse=True)

    table = TABLE(_class="centered striped")
    table.append(THEAD(TR(TH("Rank"),
                          TH("Name"),
                          TH("StopStalk Handle"),
                          TH("Institute"),
                          TH("StopStalk Rating"),
                          TH("Rating Changes"),
                          TH("Per Day Changes"))))

    tbody = TBODY()
    rank = 1
    for i in users:

        if i[5]:
            span = SPAN(_class="orange tooltipped",
                        data={"position": "right",
                              "delay": "50",
                              "tooltip": "Custom User"},
                        _style="cursor: pointer; " + \
                                "float:right; " + \
                                "height:10px; " + \
                                "width:10px; " + \
                                "border-radius: 50%;")
        else:
            span = SPAN()

        tr = TR()
        tr.append(TD(str(rank) + "."))
        tr.append(TD(DIV(span, DIV(i[0]))))
        tr.append(TD(A(i[1],
                       _href=URL("user", "profile", args=[i[1]]),
                       _target="_blank")))
        tr.append(TD(A(i[2],
                       _href=URL("default",
                                 "leaderboard",
                                 vars={"q": i[2],
                                       "global": global_leaderboard}))))
        tr.append(TD(i[3]))
        if i[6] > 0:
            tr.append(TD(B("+%s" % str(i[6])),
                         _class="green-text text-darken-2"))
        elif i[6] < 0:
            tr.append(TD(B(i[6]), _class="red-text text-darken-2"))
        else:
            tr.append(TD(i[6], _class="blue-text text-darken-2"))

        diff = "{:1.5f}".format(i[4])

        if float(diff) == 0.0:
            tr.append(TD("+" + diff, " ",
                         I(_class="fa fa-minus")))
        elif i[4] > 0:
            tr.append(TD("+" + str(diff), " ",
                         I(_class="fa fa-chevron-circle-up",
                           _style="color: #0f0;")))
        elif i[4] < 0:
            tr.append(TD(diff, " ",
                         I(_class="fa fa-chevron-circle-down",
                           _style="color: #f00;")))

        tbody.append(tr)
        rank += 1

    table.append(tbody)
    switch = DIV(LABEL(H6("Friends' Submissions",
                          INPUT(_type="checkbox", _id="submission-switch"),
                          SPAN(_class="lever pink accent-3"),
                          "Global Submissions")),
                 _class="switch")
    div = TAG[""](switch, table)
    return dict(div=div,
                heading=heading,
                global_leaderboard=global_leaderboard)

# ----------------------------------------------------------------------------
dataset/ETHPy150Open stopstalk/stopstalk-deployment/controllers/default.py/leaderboard/original