CineAnalyst AI

AI-powered movie analytics platform that turns natural-language questions into secure SQL queries and real-time insights using Gemini and ClickHouse

Comment

Inspiration## Inspiration

Movie databases contain a lot of valuable information, but getting useful insights often requires writing SQL queries or knowing how to work with data tools.

We wanted to make movie analytics accessible through natural language. With CineAnalyst AI, a user can simply ask a question such as:

Which movie generated the most revenue?

Instead of manually writing SQL, the application uses Gemini to understand the question and generate a SQL query that can be executed against ClickHouse.

What it does

CineAnalyst AI is an AI-powered movie analytics platform that connects natural-language questions with real-time analytical data.

Users can:

  • Explore a movie database through a dashboard.
  • Ask questions using natural language.
  • Automatically generate SQL queries with Gemini.
  • Execute analytical queries against ClickHouse.
  • View the generated SQL and results.
  • Explore movies, ratings, revenue, countries, genres, and release years.
  • Check dashboard statistics such as total movies, highest-rated movie, top-revenue movie, and average rating.

For example, asking:

Which movie generated the most revenue?

can produce a SQL query that identifies The Dark Knight as the highest-revenue movie in the database.

How we built it

The project uses a modern full-stack architecture:

  • React + Vite for the frontend.
  • FastAPI + Python for the backend API.
  • Gemini for natural-language-to-SQL generation.
  • ClickHouse for analytical data storage and fast SQL queries.
  • Render for deployment.
  • GitHub for source-code management.

The main workflow is:

User question
      ↓
React frontend
      ↓
FastAPI backend
      ↓
Gemini
      ↓
Generated SQL
      ↓
SQL security validation
      ↓
ClickHouse
      ↓
Analytical result
      ↓
React dashboard

We also added SQL security validation so that the AI-generated query must be a SELECT query and can only access the intended default.movies table. Destructive SQL commands and multiple statements are rejected.

Challenges

One of the main challenges was connecting an AI model to a real analytical database while keeping the generated SQL controlled and safe.

We also had to solve deployment and frontend-backend communication issues, including CORS configuration and connecting the React application to the public Render API.

Another challenge was making the experience simple enough for a user who does not know SQL. The goal was to hide unnecessary technical complexity while still allowing users to inspect the generated SQL and understand how the answer was obtained.

What we learned

This project taught us how to combine generative AI with traditional data engineering.

We learned that an AI model becomes much more useful when it is connected to structured, queryable data. We also learned the importance of validating AI-generated code before executing it against a real database.

Most importantly, we learned how to build an end-to-end AI analytics application, from natural-language input to database execution and visualization.

Future improvements

Future versions of CineAnalyst AI could include:

  • More advanced movie analytics.
  • Interactive charts and visualizations.
  • More complex analytical agents.
  • Conversation memory.
  • Additional datasets.
  • More database tables and relationships.
  • Advanced ClickHouse integrations and agentic workflows.
  • More robust evaluation of generated SQL.

CineAnalyst AI is our step toward making data analytics as simple as asking a question.

What it does

CineAnalyst AI transforms natural-language questions into actionable movie data insights.

Users can ask questions such as:

Which movie generated the most revenue?

The system uses Gemini to understand the question and generate a SQL query. The query is then validated for security and executed against ClickHouse. The results are returned to the user through a simple React dashboard.

The platform provides:

  • Natural-language movie analytics
  • AI-powered SQL generation
  • Secure SQL validation
  • Real-time ClickHouse queries
  • Movie revenue and rating analysis
  • Country and genre analysis
  • Interactive dashboard statistics
  • Generated SQL visibility for transparency

How we built it

CineAnalyst AI is built with a full-stack architecture:

  • React + Vite — frontend interface
  • FastAPI + Python — backend API
  • Gemini — natural-language-to-SQL generation
  • ClickHouse — analytical database
  • GitHub — source control
  • Render — cloud deployment

The workflow is:

User Question
      ↓
React Frontend
      ↓
FastAPI API
      ↓
Gemini
      ↓
SQL Generation
      ↓
Security Validation
      ↓
ClickHouse
      ↓
Results
      ↓
React Dashboard

We designed the system so that AI-generated SQL is validated before execution. Only SELECT queries targeting the intended movie dataset are allowed, while destructive commands and multiple SQL statements are rejected.

Challenges we ran into

One of our biggest challenges was connecting generative AI with a real analytical database while maintaining control over the SQL generated by the model.

We also faced deployment challenges when moving from a local development environment to Render. This included configuring environment variables, connecting the frontend to the public backend API, and resolving CORS issues.

Another challenge was creating a simple user experience while keeping the analytical process transparent. We wanted users to benefit from AI without hiding how their questions were translated into SQL.

Accomplishments that we're proud of

We are proud to have built a complete working AI analytics application from the ground up.

The application successfully connects:

Natural Language → Gemini → SQL → ClickHouse → Results

We also successfully deployed the backend to Render and connected the React frontend to the live API.

CineAnalyst AI can answer real analytical questions against a live movie dataset instead of simply generating hypothetical answers.

We are particularly proud of the SQL security layer, which prevents the AI from executing destructive database operations.

What we learned

This project taught us how to combine generative AI, backend APIs, and analytical databases into one complete application.

We learned that generating SQL with an AI model is only part of the problem. The generated query must also be validated, controlled, executed safely, and presented clearly to the user.

We also gained practical experience with:

  • Gemini API integration
  • FastAPI
  • ClickHouse
  • React and Vite
  • REST APIs
  • CORS
  • Cloud deployment with Render
  • GitHub-based development
  • AI-generated SQL validation

Most importantly, we learned how natural-language interfaces can make data analytics accessible to people who do not know SQL.

What's next for CineAnalyst AI

We plan to evolve CineAnalyst AI from a natural-language analytics tool into a more advanced agentic data analyst.

Future improvements include:

  • Conversational analytics with memory
  • Interactive charts generated from queries
  • More complex multi-step analytical tasks
  • Additional movie and entertainment datasets
  • Advanced ClickHouse integrations
  • Agentic workflows for deeper analysis
  • Automated insights and recommendations
  • More comprehensive SQL evaluation and testing
  • Improved observability and performance monitoring

Our long-term goal is to make CineAnalyst AI capable of acting as a reliable data analyst that can understand a question, investigate the data, perform multiple analytical steps, and explain the results clearly.

Built With

Share this project:

Updates

Submission history