How can I speed up this query on table with 65 million records
I think your biggest issue is the schema.


you should try to see if you can reduce the row footprint. For example, is nvarchar really needed? Using Varchar will cut the size in half. This may cut your processing time in half too. Is BigInt really needed for HashID? you have only 65M rows, a regular INT is more than enough and again half the size. Each byte you save will gain you 65Mb of data.
you should see if you could use separate fields instead of using text manipulations, that would simplify the query, improves integrity and most probably speed.
primary key on a nvarchar(100) is not a great idea, that should be on your identity column instead. And then make it clustered. I'm sure that will remove the need for the index hint.
instead of making (hash_value, cg_id) a primary key, set it as an unique index
make sure you have a good maintenance plan to reduce index fragmentation