Where are my downvotes?
If this is actually your first SQL you're quite good in learning new languages.

Some remarks:

The Group By is not weird, it's the SUM, you apply a conditional aggregation but the result is the same as a simple COUNT(*) (you can always get the same result using different ways in SQL).

The ORDER BY 'Downvotes' DESC will not work as expected, because single quotes specify strings in SQL, so this is not sorting by the actual number of downvotes, but by the string 'Downvotes' (which of course is the same for all rows). In your case you can simply use Downvotes, [] ([Downvotes]) or better Standard SQL double quotes ("Downvotes") are only needed for names including spaces or non-standard characters, like "foo bar?"

declare @user_id int = ##UserId:int##

SELECT
    p.Id as "Post Link",
    p.Score,
    count(*) as Downvotes
FROM
    Posts p
inner join
    Votes v on p.Id = v.PostId
WHERE
    p.OwnerUserId = @user_id
and v.VoteTypeId=3
GROUP BY
    p.Id, p.Score
ORDER BY Downvotes DESC, Score DESC