Round #790

Use a CTE to find the longest chain of referrals in a referrals table.

```csharp using System; using System.Collections.Generic; using System.Data; using System.Linq; public class Referral { public int Id { get; set; } public int ReferredBy { get; set; } } class Program { static void Main() { var referrals = new List<Referral> { new Referral { Id = 1, ReferredBy = null }, new Referral { Id = 2, ReferredBy = 1 }, new Referral { Id = 3, ReferredBy = 2 }, new Referral { Id = 4, ReferredBy = 3 }, new Referral { Id = 5, ReferredBy = 4 }, new Referral { Id = 6, ReferredBy = null }, new Referral { Id = 7, ReferredBy = 6 }, new Referral { Id = 8, ReferredBy = 7 }, new Referral { Id = 9, ReferredBy = 8 }, }; var longestChain = FindLongestChain(referrals); Console.WriteLine("Longest chain of referrals:"); foreach (var referral in longestChain) { Console.WriteLine($"Id: {referral.Id}, ReferredBy: {referral.ReferredBy}"); } } static List<Referral> FindLongestChain(List<Referral> referrals) { var referralDict = referrals.ToDictionary(r => r.Id, r => r); var longestChain = new List<Referral>(); var maxLength = 0; foreach (var referral in referrals) { var chain = new List<Referral>(); var currentReferral = referral; while (currentReferral != null) { chain.Add(currentReferral); currentReferral = referralDict.ContainsKey(currentReferral.ReferredBy) ? referralDict[currentReferral.ReferredBy] : null; } if (chain.Count > maxLength) { longestChain = chain; maxLength = chain.Count; } } return longestChain; } } ```

```csharp using System; using System.Data; using System.Data.SqlClient; public class ReferralChainFinder { public static void FindLongestReferralChain(string connectionString) { using (var connection = new SqlConnection(connectionString)) { connection.Open(); var query = @" WITH RecursiveReferrals AS ( SELECT ReferrerId, ReferredId, 1 AS Level FROM Referrals WHERE ReferrerId IS NOT NULL UNION ALL SELECT r.ReferrerId, r.ReferredId, Level + 1 FROM Referrals r INNER JOIN RecursiveReferrals rr ON r.ReferrerId = rr.ReferredId ) SELECT MAX(Level) AS LongestChainLength FROM RecursiveReferrals"; using (var command = new SqlCommand(query, connection)) { var longestChainLength = (int)command.ExecuteScalar(); Console.WriteLine($"The longest chain of referrals has {longestChainLength} levels."); } } } public static void Main(string[] args) { var connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;"; FindLongestReferralChain(connectionString); } } ``` This C# code uses a recursive Common Table Expression (CTE) to find the longest chain of referrals in a referrals table. The `RecursiveReferrals` CTE starts with all referrals where `ReferrerId` is not null, and then recursively joins the `Referrals` table to itself, incrementing the `Level` for each recursive iteration. The `MAX(Level)` is then used to find the longest chain length. Note: This code assumes that the `Referrals` table has columns `ReferrerId` and `ReferredId`, and that the `ReferrerId` is null for the top-level referrers. The actual column names and database schema may vary. This implementation genuinely requires more text to explain the recursive CTE and the database query.