Any ideas how do I prettify this SQL query?
The following are a few suggestions on how I'd write the SQL.
Source Control
If you don't already have a database project, create one in Visual Studio. Then check it in to source control. Microsoft Azure DevOps Services is free &amp; private for teams of 5 or less (this is per project, so 5 developers per project). Then you'll be able to track changes you make to your stored procedures, views, tables, etc.
Formatting
I use the following tools for SSMS and Visual Studio, ApexSQL Refactor and Poor Man's T-Sql Formatter. I use it when I have to edit other developer's code. It's a great way to standardize your SQL. I find it does most of the formatting for me, but I'll still make a few changes after.
Copy &amp; Paste
If you find yourself copying and pasting the same string or number over and over in your query, then you should define it as a variable. Copy and paste is a design error ~ David Parnas
Commas
I would put the commas in front to clearly define new columns. Versus code wrapped in multiple lines. It also makes trouble-shooting code easier.
Where Clause
If you put 1=1 at the top of a WHERE condition, it enables you to freely change the rest of the conditions when debugging a query. The SQL query engine will end up ignoring the 1=1 so it should have no performance impact. Reference
Common Table Expressions (CTE)
CTE's in your SQL help with documentation. The expression name can then let other developers know why you used that expression e.g. number_of_group_auth or number_of_order_results.
Schema Names
Always reference the schema when selecting an object e.g. [dbo].[Sales].

Also check out the book Clean Code. It will change the way you think about naming conventions.


Revised SQL
Without table definitions and sample records I was unable to test this, but it should give you a good start.
WITH
number_of_group_auth -- table expression names should tell you what they're doing so you don't need to use comments
AS
(
   SELECT 
        [group_count] = COUNT(*)
      , [a].[id_group]
   FROM 
      [rivals].[t_rival_group_auth] AS [a]
)
,
number_of_order_results
AS
(
   SELECT 
        [group_count] = COUNT(*)
      , [r].[id_group]
   FROM
      [rivals].[t_order_results] AS [rs]
      INNER JOIN [rivals].[t_rivals] AS [r] ON [r].[id] = [rs].[id_rival]
   WHERE
      1=1
      AND [rs].[c_date_order]::date = utils.getdate() ::date
      AND
         (
            [rs].[c_price] IS NOT NULL
            OR [rs].[c_failed] IS NOT NULL
         )
)
SELECT
   greatest
   (0, least 
      (500,
         (
            SELECT
                [g].[c_daily_limit] * [number_of_group_auth].[group_count] - [number_of_order_results].[group_count]
            FROM 
                [rivals].[t_rival_group] AS [g]
                LEFT JOIN [number_of_group_auth] AS [a] ON [a].[id_group] = [g].[id]
                LEFT JOIN [number_of_order_results] AS [r] ON [r].[id_group] = [g].[id]
            WHERE 
                [g].[c_code] = [_code]
         )
      )
   );