So we have this third party shipping system that is...well...user hostile at best. To add insult to injury for the users the system has been suffering increased performance problems...

You need to start by looking at the view definition of vwRequests

If all the columns you require are not contained in the non-clustered index then the database would have to do a seek and a key-lookup or a full table scan...

INSERT INTO tblWorking
(
    Col1,
    Col2,
    Col3,
    ...
)
SELECT Col1,
       Col2,
...

CREATE TABLE #Employee
(
    EmpId int NOT NULL,
    EmpName nvarchar(20)  NOT NULL
);
GO
CREATE TABLE #Swipe
(
    SwipeTime_UTC datetime  NOT...

IF OBJECT_ID('tempdb..#Data', 'U') IS NOT NULL
    DROP TABLE #Data
GO
select *
  into #Data
  from (values 
(1,'A',1),
(2,'B',1),
(3,'A',1),
(3,'A',2),
(4,'B',1),
(5,'A',1),
(5,'B',1),
(6,'A',1),
(6,'A',2),
(6,'A',3),
(7,'A',1),
(8,'A',1),
(9,'B',1)) x(Id,SubType,Ref)

GO

select Id, SubType, Ref, 
  ...

"I am trying to count the number of customers that signed up in the last hour "

select COUNT(*)
  from #CUSTOMERS
 where CREATE_DATE > DATEADD(HOUR,-1,CURRENT_TIMESTAMP)

Are you terminating the preceding statement with a semicolon?

You can start statements that begin with "WITH" with a semicolon:

;WITH ret AS(

declare @Seconds int = 31578
select @Seconds [@Seconds],
       t.Hours, 
       u.Minutes, 
    ...

If you don't already have similar indexes try these two indexes and see if there is an improvement:

create index IX_Cuentas_1 on dbo.Cuentas(idCuenta) INCLUDE (idjerarquia,Cuenta)
create index IX_Cuentas_2 on...

--DECLARE @CurrentDate as date='2020-11-25'
DECLARE @CurrentDate as date='2020-12-10'

;WITH Data (Id, WeekName, WeekDate) AS (
    SELECT 5, 'Gameweek 1', CONVERT(date,'2020-12-01')
    UNION
...

--DECLARE @CurrentDate as date='2020-11-25'
DECLARE @CurrentDate as date='2020-12-10'

;WITH Data (Id, WeekName, WeekDate) AS (
    SELECT 5, 'Gameweek 1', CONVERT(date,'2020-12-01')
    UNION
  ...

DECLARE @Data TABLE (Name nvarchar(100));
INSERT INTO @Data(Name)
VALUES 
('Microsoft Windows NT Workstation 10.0'),
('Microsoft Windows NT Workstation 10.0'),
('Microsoft Windows NT Workstation 10.0'),
('Microsoft Windows NT Server 10.0'),
('Microsoft Windows NT Workstation...

Why have you got DISTINCT in the select from the table variable, can't you make a table variable with distinct values already in it?

Create a primary key or an index...

How do you know when the job last ran?