$standing =DB::select("SELECT DISTINCT
                            users.id,
                            username,
                            SUM(points) AS pts,
                            count(users_games.user_id) AS CountGame,
                            (SELECT count(user_id) FROM users_games WHERE points=3 AND user_id=users.id ) AS win,
                            (SELECT count(user_id) FROM users_games WHERE points=0 AND user_id=users.id ) AS los,
                            (SELECT count(user_id) FROM users_games WHERE points=1 AND user_id=users.id ) AS draw
                            FROM users_games,users
                            WHERE users_games.user_id=users.id
                            AND score_home IS NOT NULL
                            AND score_away IS NOT NULL
                            AND points IS NOT NULL
                            GROUP BY users.id
                            ORDER BY pts DESC,win DESC,draw DESC,CountGame DESC");

$query= DB::table('users_games')
        ->join('users','users.id','=','users_games.user_id')
        ->where('users_games.user_id','=','users.id')
        ->whereNotNull('users_games.score_home')
        ->whereNotNull('users_games.score_away')
        ->whereNotNull('users_games.points')
        ->groupBy('users.id')
        ->orderBy('pts','desc')
        ->orderBy('win','desc')
        ->orderBy('draw','desc')
        ->orderBy('CountGame','desc')
        ->select('users.id','sum(points) as pts','count(users_games.user_id) as CountGame','(select count(user_id) from user_games where points=3 and user_id=users.id) as win','(select count(user_id) from user_games where points=0 and user_id=users.id) as los','(select count(user_id) from user_games where points=1 and user_id=users.id) as draw')
        ->get();

$query= DB::table('users_games')
    ->join('users','users.id','=','users_games.user_id')
    ->select('users.id',
             'username',
              DB::raw('SUM(points) as pts'),
              DB::raw('count(users_games.user_id) as CountGame'),
              DB::raw('(select count(user_id) from users_games where points=3 and user_id=users.id) as win'),
              DB::raw('(select count(user_id) from users_games where points=0 and user_id=users.id) as los'),
              DB::raw('(select count(user_id) from users_games where points=1 and user_id=users.id) as draw'))
    ->whereNotNull('users_games.score_home')
    ->whereNotNull('users_games.score_away')
    ->whereNotNull('users_games.points')
    ->orderBy('pts','desc')
    ->orderBy('win','desc')
    ->orderBy('draw','desc')
    ->orderBy('CountGame','desc')
    ->groupBy('users.id')
    ->get();