1

Please login or register to vote for this query.

(click on this box to dismiss)

They gave their first accept vote to you, and now they have an accepted answer

Q&A for Drupal developers and administrators

CREATE TABLE #HasAccepted (
  UserId INT NOT NULL,
  PRIMARY KEY(UserId),
  FOREIGN KEY(UserId) REFERENCES Users(Id)
);

CREATE TABLE #Result (
  Inviter INT NOT NULL,
  Invited INT NOT NULL/*,
  PRIMARY KEY(Inviter, Invited),
  FOREIGN KEY(Inviter) REFERENCES Users(Id),
  FOREIGN KEY(Invited) REFERENCES Users(Id)*/
);

INSERT INTO #HasAccepted
SELECT Posts.OwnerUserId
FROM Posts, Votes
WHERE
  Votes.VoteTypeId = 1
  AND
  Votes.PostId = Posts.Id
  AND
  Posts.OwnerUserId > 0
GROUP BY Posts.OwnerUserId;

WITH Raw AS (
  SELECT
    Q.OwnerUserId AS QUserId,
    A.OwnerUserId AS AUserId,
    Votes.Id AS VoteId
  FROM Votes, Posts Q, Posts A, #HasAccepted
  WHERE
    Votes.VoteTypeId = 1
    AND
    Votes.PostId = A.Id
    AND
    Q.Id = A.ParentId
    AND
    Q.OwnerUserId > A.OwnerUserId
    AND
    Q.OwnerUserId = #HasAccepted.UserId
),
Raw2 AS (
  SELECT QUserId, MIN(VoteId) AS MinVoteId
  FROM Raw
  GROUP BY QUserId
),
Result AS (
  SELECT Raw.AUserId AS Inviter, Raw.QUserId AS Invited
  FROM Raw, Raw2
  WHERE
    Raw.QUserId = Raw2.QUserId
    AND
    Raw.VoteId = Raw2.MinVoteId
)
/*
SELECT Inviter, COUNT(*) AS Count
FROM Result
GROUP BY Inviter
ORDER BY Count DESC, Inviter;
*/
SELECT *
FROM Result
WHERE Inviter = ##AccountId:int##

fork query

edited dec 16

created dec 16 peterh - PLEASE DELETE THIS ACCOUNT