Explicación de la estructura de la base de datos de ejemplo

Querys:

-Top 10 de películas con mayor facturación.
-Categorías con mayor facturación.
-Empleado que mas venta realiza en $.
-Ticket promedio por cliente en $.
-Contribución por categoría (Margen).

Las 10 películas más rentadas

SELECT p.titulo, COUNT(p.pelicula_id) as c FROM peliculas as P
JOIN inventarios as i ON i.pelicula_id = p.pelicula_id
JOIN rentas as r on r.inventario_id = i.inventario_id
GROUP BY p.titulo, i.pelicula_id
ORDER BY c DESC LIMIT 10

Sinceramente el Diagrama ERD generado por pgadmin es un asco

si una pelicular tiene varios idiomas y un idioma puede estar en varias películas, por que no se crea también la tabla relacional de ellas?

Reto

Me interesaria saber que peliculas, existen en cada tienda, haría un select a inventarios y haría un inner join donde pelicula_id de inventarios es igual a pelicula_id de peliculas

Considero que la siguiente información a ser extraida podría ser util:

1. Pelicula mas rentada.
2. Actor con mas peliculas.
3. Usuario con mayor rentas.
4. Usuario con mayor pagos.
5. Tiendas con mayor trabajadores.
6. Ciudades con menos usuarios.

Los actores con las categorías de películas en que más participan.

SELECT a.nombre, a.apellido, c.nombre, COUNT(p.pelicula_id)
FROM actores a
JOIN peliculas_actores pa ON a.actor_id=pa.actor_id
JOIN peliculas p ON pa.pelicula_id=p.pelicula_id
JOIN peliculas_categorias pc ON p.pelicula_id=pc.pelicula_id
JOIN categorias c ON pc.categoria_id=c.categoria_id
GROUP BY a.nombre, a.apellido, c.nombre
ORDER BY COUNT(p.pelicula_id) DESC;

Fue harta prueba y error pero di con el resultado.

súper cool todo esto :3

Me gustaría saber :
-Zona con más ventas.
-Top ten de tiendas con mayores ventas.
-Las tres tiendas con menores ventas.

• Vendedores destacados por zona.
  -Edad promedio de los clientes.
  -Los 3 géneros más rentables.
  -Actores más populares.

Se me ocurre listar las tiendas TOP en base a cuánto dinero recaudó por el alquiler.
También se me ocurre listar las películas TOP alquiladas, sin importar la tienda, luego se podría buscar las tiendas TOP en donde se alquiló poco las películas TOP, es decir, sería interesante ver por qué en algunas tiendas no se alquiló mucho las películas top.

Este es mi Query para calcular el Top 10 de peliculas con mas alta facturacion.

SELECT pelicula_id, titulo, SUM(cantidad)
FROM (
	SELECT pe.pelicula_id, pe.titulo, pa.cantidad FROM public.peliculas AS pe
INNER JOIN public.inventarios AS i  ON pe.pelicula_id = i.pelicula_id
INNER JOIN public.rentas AS r ON i.inventario_id = r.inventario_id
INNER JOIN public.pagos AS pa ON r.renta_id = pa.renta_id
) AS Tabla_facturacion
GROUP BY pelicula_id, titulo
ORDER BY SUM(cantidad) desc
LIMIT 10;

Por si eres principiante, como yo, aquí van algunas consultas de nivel básico (MUY BÁSICO) para calentar:

Película con mayor precio de renta

Top 5 de peliculas con mayor precio de renta

Top 3 de películas con mayor duración donde incluya la clasificación

¿Qué empleado ha registrado el mayor numero de pagos?

SELECT COUNT(pagos.pago_id) as pagos_x_empleado, CONCAT(empleados.nombre, ' ', empleados.apellido) as trabajador
FROM pagos
INNER JOIN empleados ON pagos.empleado_id = empleados.empleado_id
GROUP BY empleados.empleado_id
ORDER BY pagos_x_empleado DESC;

Excelente, el manejo de cursores

![](

Un reporte de ingresos por tienda en los últimos 6 meses

Me gustaría conocer cuáles son la películas más rentadas por estación del año

Películas Más Rentadas

SELECT
    p.pelicula_id,
    p.titulo,
    COUNT(*) AS veces_rentada
FROM
    public.peliculas p
JOIN
    public.inventarios i ON p.pelicula_id = i.pelicula_id
JOIN
    public.rentas r ON i.inventario_id = r.inventario_id
GROUP BY
    p.pelicula_id, p.titulo
ORDER BY
    veces_rentada DESC
LIMIT 10;

Películas de Mayor Duración

SELECT
    pelicula_id,
    titulo,
    duracion
FROM
    public.peliculas
ORDER BY
    duracion DESC
LIMIT 10;

Yo utilice un join para saber la cantidad de peliculas disponibles segun el inventario.

select peliculas.pelicula_id, peliculas.titulo, inventario_id
from peliculas
join inventarios
on peliculas.pelicula_id= inventarios.pelicula_id;

Se me ocurre que estaría interesante ver cuales son las ciudades con menos rentas, y luego ver cuales son las mejores y peores sucursales de esas ciudades para contemplar la posibilidad de cerrar algunas o combinarlas con otras.

También podría ser ver que que clasificación se vende más en cada ciudad, para ver si vale la pena quitar algunas películas en ciertas sucursales.

Ver que actores aparecen más para contemplar hacer una campaña de marketing con ellos

Ver que clientes han hecho menos rentas para contemplar hacer una estrategia de marketing por correo o newsletter para fortalecer la fidelidad de los clientes

Les dejo una aplicacion para conectarse a la base de datos y extraer y visualizar el modelo de la misma.

https://dbschema.com/

-top 5 clientes mas frecuentes

• top tiendas con mas ventas
  -promedio de edad de clientes
  -películas mas rentadas

Se me ocurre que se le puede hacer todo tipo de select. Para filtrar diferentes datos segun lo que se nos pida.

**Ejersicio de esta clase: **

-Categoria de peliculas mas vendidas
-Peliculas que no se venden x cantidad de tiempo, para oofrcer promos
-Clientes que mas compran para establecer categorias y ofrecer promos
-Fechas con mayores ventas
-Tiendas que mas ventas generan

Querys
Peliculas mas rentadas
Clientes que mas rentan peliculas
Empleados que mas rentan peliculas
Clasificacion mas rentada
Ingresos
Ciudad donde mas se rentan peliculas
Duracion promedio de peliculas mas rentadas

• Cuantos actores han trabajado en películas
• la tienda donde rentan mas películas y menos.

Ventas realizadas por cada empleado
Tengo mis dudas, me arroja que la facturación más alta es de 31059 y lo hago de otra forma y me arroja poco más de 30000. Este es el código

-- Empleados con mayor facturacion
SELECT CONCAT(nombre,' ', apellido) AS full_name, SUM(cantidad) AS sales
FROM pagos
INNER JOIN empleados USING (empleado_id)
GROUP BY full_name
ORDER BY sales DESC;

Cantidad de películas por categorías

SELECT c.nombre, 
	COUNT(pc.categoria_id) AS cat
FROM categorias AS c
JOIN peliculas_categorias AS pc ON pc.categoria_id = c.categoria_id
GROUP BY c.nombre, pc.categoria_id
ORDER BY cat DESC
LIMIT 10
;

Para una estrategia de penetracion de mercado:
-Top paises con mayor cantidad de compra(sea 5 paises o 10)
-Tipo de peliculas que consumen en cada paises
-Actores asociados

Estrategia de alto nivel de mercado:
-Top recaudacion de peliculas
-Top peliculas consumidas
-Costo de peliculas con mas recaudacion
-Top peliculas mas vistas
(en caso de querer generar una pelicula redituable)
-Actores asociados a peliculas mas vistas y consumidas
-Tematica de pelicula

• Ventas por ciudad
• Ventas por tienda
• Ventas por año
• Categorías más vendidas
• Directores más eficientes según ventas
• Ventas por idioma película

Queries:
-Top10 películas más rentadas
-Bottom 5 de tiendas en revenue
-Rentas por sexo/género de los clientes
-Costo promedio del ticket final
-Top 5 actores y Top 5 actrices que más se rentan
-Inventario en $$$ vs Bottom 5 vs Top5 de tiendas en revenue

1. Qué ciudades rentan más películas
2. Qué (categoría o película) trae mayor dinero a la tienda
3. Qué empleado ganó más dinero
4. Duración media de cada película

esta muy buena la bd para mejorar la skills !!

Algunas sugerencias:

• Las películas con mayor y menor precio de renta
• Los actores con mayor número de películas y un patrón de participación de acuerdo a la clasificación
• Un mapa de distribución para ver la relación entre tiendas y empleados, con la posibilidad de mejorar el transporte (si es una tienda física)

Se me ocurre hacer este query para saber que tipo de película es rentada por ciudad, podríamos ser más específicos y hacer una consulta por sector demográfico. Esto para que nos de una idea que tipo de película rentan nuestros clientes según el sector demográfico.

Podríamos cruzar está información con la de la población y preparar nuestro inventario a través de un análisis de la demanda basada en estos datos, el tipo de clasificación que buscan nuestro clientes según su ciudad o sector demográfico (podríamos buscar la edad de quienes rentan las películas y agruparla en rangos).

SELECT	ciudad,
	COUNT(clasificacion) AS peliculas_rentadas,
	clasificacion
FROM peliculas AS p
INNER JOIN inventarios AS i ON p.pelicula_id = i.pelicula_id
INNER JOIN rentas AS r ON i.inventario_id = r.inventario_id
INNER JOIN clientes AS c ON r.cliente_id = c.cliente_id
INNER JOIN direcciones AS d ON c.direccion_id = d.direccion_id
INNER JOIN ciudades AS ci ON d.ciudad_id = ci.ciudad_id
GROUP BY ciudad, clasificacion
ORDER BY peliculas_rentadas DESC;

Se podría obtener, el mayor número de peliculas vendidas por género por ciudad para obtener una mejor perspectiva de los gustos regionales, tambien se podría obtener las rentas con las ventas por categoria por ciudad y por tienda. Se puede obtener el precio con el tipo de cambio y juntarlo con los pagos totales para saber la rentabilidad total. Se podría obtener las peliculas más vendidas por lenguaje tambien para conocer los gustos de la región en lenguaje de visualización.

A seguir aprendiendooooo

Hice de esta forma las 10 mas rentadas:

SELECT peliculas.pelicula_id, peliculas.titulo, SUM(pagos.cantidad) AS Mas_Rentada
FROM peliculas
INNER JOIN inventarios ON peliculas.pelicula_id = inventarios.pelicula_id
INNER JOIN rentas ON inventarios.inventario_id = rentas.inventario_id
INNER JOIN pagos ON rentas.renta_id = pagos.renta_id
GROUP BY peliculas.pelicula_id
ORDER BY Mas_Rentada DESC
LIMIT 10

Si hay algo mal les pido si me avisan.
Gracias.

interesante

¿Cómo puedo obtener si una película tiene varios idiomas con las relaciones que existen?

• Películas mas rentadas
• Cantidad promedio de películas rentadas por cliente
• Cantidad de rentas por Empleado
• Películas no retornadas a la tienda
• Top ten películas por categoría

Se pueden geolocalizar las tiendas de manera que se sepa por sector qué tipo de películas se rentan más. Cruzando esa info, por ejemplo, con variables sociodemográficas del tipo de clientes.

Una consulta podría ser conocer cual es la película mas rentada en el último año por ciudad.

Muy bien explicado.
Solo una acotación, podrian tener otro script de carga de los datos desde insert, ya que si se tiene una base de datos desde docker, se pone complejo cargar un dump desde docker.

• Peliculas más rentadas con sus ingresos
• Categorías con más peliculas
• Actores con más participaciones

Top 10 de clientes con mas rentas de películas realizadas:

select count(*) cantidad_compras, CONCAT(nombre, ’ ', apellido) as "Nombre del Cliente"
from rentas
inner join clientes using(cliente_id)
group by cliente_id
order by cantidad_compras desc
limit 10

Variación del tipo de cambio en un trimestre económico.

Qué tipo de peliculas han pagado más los clientes a lo largo de los años
Qué empleado ha generado más rentas
Precio de peliculas más solicitadas
Direccion de los clientes que piden mas peliculas (Para identificar esa Region)