-- *** Test Data ***
-- Please pprovide consumable test data in future
CREATE TABLE #t
(
	From_UserId int NOT NULL
	,To_UserID int NOT NULL
	,[date] date NOT NULL
);
INSERT INTO #t
VALUES (3953, 6274, '20141022')
	,(3953, 6152, '20141022')
	,(1112, 2710, '20141022')
	,(3953, 1851, '20141023')
	,(3953, 4302, '20141023')
	,(4302, 2710, '20141023');
-- *** End Test Data ***

WITH SentFiles
AS
(
	SELECT [date], From_UserId, COUNT(*) AS Files
	FROM #t
	GROUP BY [date], From_UserId
)
,ReceivedFiles
AS
(
	SELECT [date], To_UserID, COUNT(*) AS Files
	FROM #t
	GROUP BY [date], To_UserID
)
SELECT COALESCE(S.[date], R.[date]) AS [date]
	,COALESCE(S.From_UserId, R.To_UserID) AS UserID
	,COALESCE(S.Files, 0) AS FilesSent
	,COALESCE(R.Files, 0) AS FilesReceived
FROM SentFiles S
	FULL JOIN ReceivedFiles R
		ON S.[date] = R.[date]
			AND S.From_UserId = R.To_UserID
ORDER BY [date], UserID;