// Optimal SQL solution
#include <stdio.h>
#include <string.h>

char* solve_friday_purchases_optimal() {
    static char query[] = 
        "SELECT \n"
        "  FLOOR((DAY(purchase_date) - 1) / 7.0) + 1 as week_of_month,\n"
        "  SUM(amount_spend) as total_amount\n"
        "FROM Purchases\n"
        "WHERE DAYOFWEEK(purchase_date) = 6\n"  /* Friday */
        "  AND purchase_date >= '2023-11-01'\n"
        "  AND purchase_date <= '2023-11-30'\n"
        "GROUP BY week_of_month\n"
        "ORDER BY week_of_month;";
    return query;
}

// Alternative using modular arithmetic
char* solve_friday_purchases_modular() {
    static char query[] = 
        "WITH november_fridays AS (\n"
        "  SELECT \n"
        "    purchase_date,\n"
        "    amount_spend,\n"
        "    FLOOR((DAY(purchase_date) - 1) / 7) + 1 as week_of_month\n"
        "  FROM Purchases\n"
        "  WHERE WEEKDAY(purchase_date) = 4\n"  /* Friday (0=Monday) */
        "    AND YEAR(purchase_date) = 2023\n"
        "    AND MONTH(purchase_date) = 11\n"
        ")\n"
        "SELECT week_of_month, SUM(amount_spend) as total_amount\n"
        "FROM november_fridays\n"
        "GROUP BY week_of_month\n"
        "ORDER BY week_of_month;";
    return query;
}

int main() {
    printf("Optimal Solution:\n%s\n", solve_friday_purchases_optimal());
    printf("\nAlternative Solution:\n%s\n", solve_friday_purchases_modular());
    return 0;
}