Several joins in query - possible to replacement to gain performance?

Adding an index to vendor_info reduces duration of your 2nd query from over 300ms to under 30ms average over repeated runs

PRIMARY KEY CLUSTERED (id, datestamp)

Changing the 2 step process into a CTE reduces total duration even more to well under 15ms average over repeated runs.

The CTE method lets the query optimiser use the new primary key

CREATE TABLE vendor_info (
  id INT,
  datestamp INT,
  statuz INT,
  maintainer VARCHAR(25)

  PRIMARY KEY CLUSTERED (id, datestamp)
);

  INSERT INTO vendor_info VALUES (1, 20180101, 0, 'Jay');
  INSERT INTO vendor_info VALUES (2, 20180101, 0, 'Eric');
  INSERT INTO vendor_info VALUES (3, 20180101, 1, 'David');
  INSERT INTO vendor_info VALUES (1, 20180201, 1, 'Jay');
  INSERT INTO vendor_info VALUES (2, 20180201, 0, 'Jay');
  INSERT INTO vendor_info VALUES (3, 20180201, 1, 'Jay');
  INSERT INTO vendor_info VALUES (1, 20180301, 1, 'Jay');
  INSERT INTO vendor_info VALUES (2, 20180301, 1, 'David');
  INSERT INTO vendor_info VALUES (3, 20180301, 1, 'Eric');

WITH vendor_dates AS
(SELECT
   id
  , MIN(datestamp) AS min_datestamp
  , MAX(datestamp) AS max_datestamp
  , MAX(case when statuz = 0 then datestamp end) AS max_s0_date
  , MAX(case when statuz = 1 then datestamp end) AS max_s1_date
  , MIN(case when statuz = 0 then datestamp end) AS min_s0_date
  , MIN(case when statuz = 1 then datestamp end) AS min_s1_date
 FROM vendor_info
 GROUP BY id
 )
 SELECT 
    vd.id
  , v1.maintainer  AS first_maintainer
  , v2.maintainer AS last_maintainer 
  , v3.maintainer AS last_s0_maintainer
  , v4.maintainer AS last_s1_maintainer
  , v5.maintainer AS first_s0_maintainer
  , v6.maintainer AS first_s1_maintainer

FROM vendor_dates vd
LEFT JOIN vendor_info v1 ON vd.id = v1.id AND vd.min_datestamp = v1.datestamp
LEFT JOIN vendor_info v2 ON vd.id = v2.id AND vd.max_datestamp = v2.datestamp
LEFT JOIN vendor_info v3 ON vd.id = v3.id AND vd.max_s0_date = v3.datestamp
LEFT JOIN vendor_info v4 ON vd.id = v4.id AND vd.max_s1_date = v4.datestamp
LEFT JOIN vendor_info v5 ON vd.id = v5.id AND vd.min_s0_date = v5.datestamp
LEFT JOIN vendor_info v6 ON vd.id = v6.id AND vd.min_s1_date = v6.datestamp;

Check the following query.

WITH
  a AS (
    SELECT
      id, datestamp, maintainer, statuz,
      MIN(datestamp) OVER(PARTITION BY id) AS fm,
      MAX(datestamp) OVER(PARTITION BY id) AS lm,
      MIN(datestamp) OVER(PARTITION BY id, statuz) AS fZm,
      MAX(datestamp) OVER(PARTITION BY id, statuz) AS lZm
    FROM vendor_info
  )
SELECT
  id,
  MIN(IIF(datestamp = fm, maintainer, NULL)) AS  first_maintainer,
  MAX(IIF(datestamp = lm, maintainer, NULL)) AS last_maintainer,
  MAX(IIF(datestamp = lZm AND statuz = 0, maintainer, NULL)) AS last_s0_maintainer,
  MAX(IIF(datestamp = lZm AND statuz = 1, maintainer, NULL)) AS last_s1_maintainer,
  MIN(IIF(datestamp = fZm AND statuz = 0, maintainer, NULL)) AS first_s0_maintainer,
  MIN(IIF(datestamp = fZm AND statuz = 1, maintainer, NULL)) AS first_s1_maintainer
FROM a
GROUP BY id;

It can be tested on SQL Fiddle.

I haven't had time yet to generate 10 mil test records , but try this with index on Id, datestamp - I've got hopes for it - the execution plan looked good - edit - with 50 mil records I generated, it looked pretty fast as long as the (id,datestamp) index (or other suitable index) is there.

SELECT tID.id, V1.first_maintainer, V2.last_maintainer, V3.last_s0_maintainer, V4.last_s1_maintainer, V5.first_s0_maintainer, V6.first_s1_maintainer
        FROM (SELECT DISTINCT ID from vendor_info) tID 
            OUTER APPLY 
                    (SELECT TOP 1 vi1.maintainer first_maintainer 
                                FROM vendor_info vi1 
                                        WHERE vi1.id = tID.id 
                                            ORDER BY vi1.datestamp ASC) V1
            OUTER APPLY 
                    (SELECT TOP 1 vi2.maintainer last_maintainer  
                                FROM vendor_info vi2 
                                        WHERE vi2.id = tID.id 
                                            ORDER BY vi2.datestamp DESC) V2
            OUTER APPLY 
                    (SELECT TOP 1 vi3.maintainer last_s0_maintainer  
                                FROM vendor_info vi3 
                                        WHERE vi3.statuz = 0 AND vi3.id = tID.id 
                                            ORDER BY vi3.datestamp DESC) V3
            OUTER APPLY 
                    (SELECT TOP 1 vi4.maintainer last_s1_maintainer  
                                FROM vendor_info vi4 
                                        WHERE vi4.statuz = 1 AND vi4.id = tID.id 
                                            ORDER BY vi4.datestamp DESC) V4
            OUTER APPLY 
                    (SELECT TOP 1 vi5.maintainer first_s0_maintainer  
                                FROM vendor_info vi5 
                                        WHERE vi5.statuz = 0 AND vi5.id = tID.id 
                                            ORDER BY vi5.datestamp ASC) V5
            OUTER APPLY 
                    (SELECT TOP 1 vi6.maintainer first_s1_maintainer  
                                FROM vendor_info vi6 
                                        WHERE vi6.statuz = 1 AND vi6.id = tID.id 
                                            ORDER BY vi6.datestamp ASC) V6

I'd go with Andrei Odegov's answer.

The perfect solution would be an aggregation function that gives you the name for the maximum or minimum date, like Oracle's KEEP FIRST/LAST. SQL Server doesn't feature such function, so using window functions as shown by Andrei Odegov seems the best solution.

If this is still too slow, it may be worth a try to concatenate days and names and look for MIN/MAX of these (e.g. '20180101Eric' < '20180201Jay'), then extract the names. A lot of string manipulation, but simple aggregation, and you must read the whole table anyway.

WITH vi AS
(
  SELECT
    id,
    statuz,
    CONVERT(VARCHAR, datestamp) + maintainer AS date_and_name
  FROM vendor_info
)
SELECT
   id
  , SUBSTRING(MIN(date_and_name), 9, 100) AS first_maintainer
  , SUBSTRING(MAX(date_and_name), 9, 100) AS last_maintainer
  , SUBSTRING(MAX(case when statuz = 0 then date_and_name end), 9, 100) AS last_s0_maintainer
  , SUBSTRING(MAX(case when statuz = 1 then date_and_name end), 9, 100) AS last_s1_maintainer
  , SUBSTRING(MIN(case when statuz = 0 then date_and_name end), 9, 100) AS first_s0_maintainer
  , SUBSTRING(MIN(case when statuz = 1 then date_and_name end), 9, 100) AS first_s1_maintainer
FROM vi
GROUP BY id
ORDER BY id;

(If you store the dates as dates and not as integers as shown in your SQL fiddle, then you'll have to change CONVERT and maybe SUBSTRING accordingly.)

SQL fiddle: http://sqlfiddle.com/#!18/9ee2c7/46

Also it's possible to use UNPIVOT/JOIN/PIVOT:

WITH
  a AS (
    SELECT
      id, statuz,
      MIN(datestamp) AS fzm, MAX(datestamp) AS lzm,
      MIN(MIN(datestamp)) OVER(PARTITION BY id) AS fm,
      MAX(MAX(datestamp)) OVER(PARTITION BY id) AS lm
    FROM vendor_info
    GROUP BY id, statuz
  ),
  b AS (
    SELECT
      v.id,
      up.[type] + IIF(up.[type] IN('fm', 'lm'), '', STR(up.statuz, 1)) AS p,
      v.maintainer
    FROM a
    UNPIVOT(datestamp FOR [type] IN(fm, lm, fzm, lzm)) AS up
    JOIN vendor_info v
      ON up.id = v.id AND up.datestamp = v.datestamp
  )
SELECT
  id,
  fm AS first_maintainer, lm AS last_maintainer,
  lzm0 AS last_s0_maintainer, lzm1 AS last_s1_maintainer,
  fzm0 AS fzmfirst_s0_maintainer, fzm1 AS first_s1_maintainer
FROM b
PIVOT(MIN(maintainer) FOR p IN(fm, lm, lzm0, lzm1, fzm0, fzm1)) AS p;

It can be tested on SQL Fiddle.